if its 2008r2 research tableDiff. Its amazing. The source and destination tables must be identical in structure. I use a .bat file running on a windows Scheduled task to sync databases from one server/DB to another every night. Here is the .bat file. Only very small changes on lines 13-28 are required to use it, they should be very obvious.
Note - No changes are required to the :processTable function at the bottom
Code:
@ECHO OFF
color c0
echo.********************************************************************************
echo. Starting %CD% %date% %time%
echo ********************************************************************************
echo.
echo ***********************************************************
echo * Set up tablediff and logs paths
echo ***********************************************************
echo.
SET folderDiffs=D:\Inetpub\Scripts\temp\folderToSavwSQLFiles
SET LogFolder=D:\Inetpub\Scripts\Logs\folderNameForLog
SET exeTableDiff="C:\Program Files\Microsoft SQL Server\100\COM\Tablediff.exe"
SET exeSqlCmd="C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd.exe"
SET sourceServer=ServerName
SET sourceDatabase=DBName
SET sourceUser=UName
SET sourcePassword=pWord
SET destinationServer=ServerName
SET destinationDatabase=DBName
SET destinationUser=UName
SET destinationPassword=pWord
SET tables=tableOne, tableTwo, tableThree
for %%i in (%tables%) do (call :processTable %%i)
echo.********************************************************************************
echo. Finished %date% %time%
echo ********************************************************************************
echo.
goto :end
:processTable
SET sourceTable=%1
SET destinationTable=%sourceTable%
echo.********************************************************************************
echo. Starting %date% %time%
echo.
echo. Processing %sourceTable%
echo.********************************************************************************
del /f /q %folderDiffs%\diffs_%sourceTable%.sql
%exeTableDiff% -sourceserver %sourceServer% -sourcedatabase %sourceDatabase% -sourcetable %sourceTable% -sourceuser %sourceUser% -sourcepassword %sourcePassword% -destinationserver %destinationServer% -destinationdatabase %destinationDatabase% -destinationtable %destinationTable% -destinationuser %destinationUser% -destinationpassword %destinationPassword% -f %folderDiffs%\diffs_%sourceTable%.sql > %LogFolder%\diffs_%sourceTable%.log
If Exist %folderDiffs%\diffs_%sourceTable%.sql %exeSqlCmd% -S %destinationServer% -d %destinationDatabase% -U %destinationUser% -P %destinationPassword% -i %folderDiffs%\diffs_%sourceTable%.sql
:end
DTS Package Equivalent
