tablediff.exe – Compare and Synchronize your SQL Server tables

Need to check if your tables in 2 different servers are out-of-sync? SQL Server comes with a command line tool that does the job. It’s called tablediff.exe (read up BOL entry for this nifty tool).

By default, you can find this in the COM directory of your SQL Server install folder.

In SQL Server 2005 by default it will be in:
C:Program FilesMicrosoft SQL Server90

In SQL Server 2008 / SQL Server 2008 R2 by default it will be in:
C:Program FilesMicrosoft SQL Server100

These are the switches you can specify for the tool:

Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:UsersAdministrator>cd C:Program FilesMicrosoft SQL Server100COM

C:Program FilesMicrosoft SQL Server100COM>tablediff.exe /?
Microsoft (R) SQL Server Replication Diff Tool
Copyright (c) 2008 Microsoft Corporation

User-specified agent parameter values:

 Replication Diff Tool Command Line Options

        usage: tablediff

          -- Source Options --
 -sourceserver          Source Host
 -sourcedatabase        Source Database
 -sourceschema          Source Schema Name
 -sourcetable           Source Table or View
 -sourceuser            Source Login
 -sourcepassword        Source Password
 -sourcelocked          Lock the source table/view durring tablediff

          -- Destination Options --
 -destinationserver     Destination Host
 -destinationdatabase   Destination Database
 -destinationschema     Destination Schema Name
 -destinationtable      Destination Table or View
 -destinationuser       Destination Login
 -destinationpassword   Destination Password
 -destinationlocked     Lock the destination table/view durring tablediff

          -- Misc Options --
 -t             Timeout
 -c             Column Level Diff
 -f             Generate Fix SQL (You may also specify a file name and path)
 -q             Quick Row Count
 -et            Specify a table to put the errors into
 -dt            Drop the error table if it exists
 -o             Output file
 -b             Number of bytes to read for blob data types
 -strict        Strict compare of source and destination schema
 -rc            Number of retries
 -ri            Retry interval

Here is an example usage. Note this has to be all in one line at the Command Prompt (I just separated them out into different lines for clarity):

C:Program FilesMicrosoft SQL Server100COM>tablediff.exe -sourceserver POSEIDON 
-sourcedatabase AdventureWorks2008R2 
-sourcetable Source_Department 
-destinationserver ZEUS 
-destinationdatabase AdventureWorks2008R2 
-destinationtable Destination_Department 
-f C:Tempsynctables_script.sql

The option -f allows me to specify a file name that will contain the SQL statements I need to run on the destination to “fix” and “synchronize” my tables.
Here is an example output:

Microsoft (R) SQL Server Replication Diff Tool
Copyright (c) 2008 Microsoft Corporation

User-specified agent parameter values:
-sourceserver POSEIDON
-sourcedatabase AdventureWorks2008R2
-sourcetable Source_Department
-destinationserver POSEIDON
-destinationdatabase AdventureWorks2008R2
-destinationtable Destination_Department
-f C:Tempsynctables_script.txt

Table [AdventureWorks2008R2].[dbo].[Source_Department] on POSEIDON and Table [AdventureWorks2008R2].
[dbo].[Destination_Department] on POSEIDON have 2 differences.
Fix SQL written to C:Tempsynctables_script.sql.
Err     DepartmentID    Col
Mismatch        5       Name
Src. Only       18
The requested operation took 0.4160742 seconds.

This is the resulting “fix” file:

-- Database: [AdventureWorks2008R2]
-- Table: [dbo].[Destination_Department]
SET IDENTITY_INSERT [dbo].[Destination_Department] ON

UPDATE [dbo].[Destination_Department] 
SET [Name]=N'Purchasing and Logistics' 
WHERE [DepartmentID] = 5

INSERT INTO [dbo].[Destination_Department] 
VALUES (18,N'New Department Group',N'2010-08-01 00:00:00.000',N'New Department')

SET IDENTITY_INSERT [dbo].[Destination_Department] OFF

5 thoughts on “tablediff.exe – Compare and Synchronize your SQL Server tables

  1. Peter November 15, 2010 / 6:40 am

    Have you tried Open DBDiff as an alternative to tablediff? I am currently using it for a project I am working on and I am very impressed by how powerful it is. It does what I used to use tablediff for and much more.


    • belle November 15, 2010 / 9:34 am

      No I havent, but will definitely look it up and give it a try. Thanks!


  2. chrisis January 29, 2012 / 5:27 am

    Used it with -o option and it reported rows in destination table that is not source table but when I verified the rows existed in both. It appeared to get the source rows that were not in destination table. The PK column data type is guid(uniqueidentifier) in my table in case anyone is curious. Lost faith in this tool after that.


  3. tony May 30, 2012 / 10:53 am

    RE: Open DBiff

    Peter, Great tool! I had a problem migrating a DB and it found the discrepancy. Thanks for posting


Leave a Reply

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

You are commenting using your 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