[MSSQL] SQL Server tablediff command line utility

Problem

One problem that DBAs often face is maintaining lookups tables across multiple servers or sites.  These tables could either be replicated or manually updated, but in any case sometimes the data in these tables get out of synch.  In a previous tip we talked about SQL Server comparison tools that allowed you to compare database objects or even the data itself between two different databases.  These tools are great and definitely offer a lot of advantages,  but there is a tool in SQL Server that might help as well. 

Solution

SQL Server comes with many GUI and command line tools and one new tool that is available is the tablediff command.  This utility allows you to compare the contents of one table against another table to identity if there are differences as well as create the T-SQL commands to get the tables back in synch.

The one problem with this command is that it is a command line tool, there is not GUI so writing the command is a bit tedious.  You can create a batch file and execute the batch file, but still it would be nice if there were a GUI.

The command takes a few basic parameters to run.

• sourceserver
• sourceuser
• sourcepassword
• sourcedatabase
• sourcetable
• destinationserver
• destinationuser
• destinationpassword
• destinationdatabase
• destinationtable

Here is an example command that compares two tables Table1 and Table2 in the same database.

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver server1 -sourceuser user1 -sourcepassword password1 -sourcedatabase test -sourcetable table1 -destinationserver server1 -destinationuser user2 -destinationpassword password2 -destinationdatabase test -destinationtable table2
 

When the command is run this is the output that you get:

Microsoft (R) SQL Server Replication Diff Tool
Copyright (C) 1988-2005 Microsoft Corporation. All rights reserved.

User-specified agent parameter values:
-sourceserver server1

-sourceuser user1

-sourcepassword password1
-sourcedatabase test
-sourcetable table1
-destinationserver server2

-destinationuser user2

-destinationpassword password2
-destinationdatabase test
-destinationtable table2

Table [test].[dbo].[table1] on server1 and Table [test].[dbo].[table2] on server1 have 3 differences.
Err PersonID
Mismatch 1
Dest. Only 2
Src. Only 3
The requested operation took 0.4375 seconds.
 

From this basic command we can see there are differences, but it is not very helpful as to what the problem is, so to make this more useful we can use the "-et" argument to see the differences in a table.  The "et" parameter will create a table, in our case called "Difference", so we can see the differences in a table.

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver server1 -sourceuser user1 -sourcepassword password1 -sourcedatabase test -sourcetable table1 -destinationserver server2 -sourceuser user2 -destination password2 -destinationdatabase test -destinationtable table2 -et Difference
 

When we query the table "Difference" that was created we see the following results.  As you can see this is not any more helpful then the first run.

PersonId	MSdifftool_ErrorCode	MSdifftool_ErrorDescription
1	0	Mismatch
2	1	Dest. Only
3	2	Src. Only
NULL	NULL	NULL

Another option is to use the "-f" argument that will create a T-SQL script to synchronize the two tables.

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver server1 -sourceuser user1 -sourcepassword password1 -sourcedatabase test -sourcetable table1 -destinationserver server2 -destinationuser user2 -destinationpassword password2 -destinationdatabase test -destinationtable table2 -et Difference -f c:\table1_differences.sql
 

This is the output we get from the file that is created "c:\table1_differences.sql"

-- Host: server1
-- Database: [test]
-- Table: [dbo].[table2]
UPDATE [dbo].[table2] SET [LastName]=NULL WHERE [PersonID] = 1
DELETE FROM [dbo].[table2] WHERE [PersonID] = 2
INSERT INTO [dbo].[table2] ([FirstName],[LastName],[PersonID]) VALUES ('Bob','Jones',3)
 

From here we can see the exact differences as well as having a script that we can run against Table2 to make the contents identical to Table1.

Next Steps

• This is a brief introduction of this new tool and how it can be used, check out tablediff in books online for additional options
• Add this new tool to your SQL toolbox and deploy where you need to keep certain tables in synch.
• In order to run this command you need to have primary keys setup on both tables.

 

Source:

• http://www.mssqltips.com/sqlservertip/1073/sql-server-tablediff-command-line-utility/