"No one is harder on a talented person than the person themselves" - Linda Wilkinson ; "Trust your guts and don't follow the herd" ; "Validate direction not destination" ;

June 26, 2014

Tablediff - Quickly compare column data and schema between two Database Tables on Different Machines

Today Learning's 
  • Tablediff gets installed with replication features
  • Prepare Tablediff for multiple tables
  • Run it from a batch file and log results in text file
  • One Click Results Displayed :). Tested on SQL 2012
Example Batch File 1

cd "C:\Program Files\Microsoft SQL Server\110\COM"
tablediff.exe -sourceserver SourceDBServerIP -sourcedatabase TestDB -sourcetable Table1 -destinationserver DestDBServerIP  -destinationdatabase TestDB -destinationtable Table1 -c -dt DiffsTable1
tablediff.exe -sourceserver SourceDBServerIP -sourcedatabase TestDB -sourcetable Table2 -destinationserver DestDBServerIP  -destinationdatabase TestDB -destinationtable Table2 -c -dt DiffsTable2

  • Option –c - Perform column level comparisons
  • Option –dt – Output different records to particular table (drop and create if exists)
  • By checking the log and the diff tables created we can find error code and description of differences
  • By adding -strict option it expects the same schema (column order). I have not used it in above example
  • Add -sourceuser,  -sourcepassword , -destinationuser, -destinationpassword parameters in case of sql credentials. If not specified windows credentials will be used
Batch File 2 - Capture Log Results

C:\\TableDiff.bat > C:\\TableDiffResults.txt 2>&1

One Click RunTD.bat and check logs for results. SO link

Happy Learning!!!

No comments: