Sqlserver hidden utility which can be used to compare data between tables of more then one server and can be used in Replication.

Posted: February 2, 2009 in Sqlserver
The tablediff.exe utility is used to compare the data in two tables, and is particularly useful for troubleshooting non-convergence in a replication topology. This utility can be used from the command prompt or in a batch file to perform the following tasks:
  • A row by row comparison between a source table in an instance of Microsoft SQL Server acting as a replication Publisher and the destination table at one or more instances of SQL Server acting as replication Subscribers.
  • Perform a fast comparison by only comparing row counts and schema. 
  • Compare destination tables at multiple destination servers simultaneously. 
  • Perform column-level comparisons. 
  • Generate Transact-SQL scripts to fix discrepancies at the destination server to bring the source and destination tables into convergence. 
  • Log results to an output file or into a table in the destination database. 
C:\Program Files\Microsoft SQL Server\90\COM>tablediff  -sourceserver H1A-5F201-FDG3 -sourcedatabase perfmonitor -sourcetable counterdata -destinationserver H1A-5F201-FDG3 -destinationdatabase ReplicationDB -destinationtable counterdata
Microsoft (R) SQL Server Replication Diff Tool
Copyright (C) 1988-2005 Microsoft Corporation. All rights reserved.
User-specified agent parameter values:
-sourceserver H1A-5F201-FDG3
-sourcedatabase perfmonitor
-sourcetable counterdata
-destinationserver H1A-5F201-FDG3
-destinationdatabase ReplicationDB
-destinationtable counterdata
Table [perfmonitor].[dbo].[counterdata] on H1A-5F201-FDG3 and Table [Replication
DB].[dbo].[counterdata] on H1A-5F201-FDG3 are identical.
The requested operation took 2.5 seconds.
  1. The tablediff.exe is pretty good, but there are third-party tools which can compara and synchronize table data much easier, faster and more reliable. Search for e.g. “Data Compare Azure”.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s