Compare tables between 2 databases with Tablediff and PowerShell

Few days ago, I needed to compare tables and find all differences in data between 2 databases on different SQL Server instances. For that task I decided to use the Tablediff utility which is included with SQL Server.

Tablediff utility can be used for schema and row counts comparison or a row by row comparison of data between 2 tables. It can generate T-SQL script to fix differences between 2 tables.  More information about tablediff utility you can find in BOL.

By default, tablediff utility can be found in the COM directory of SQL Server install folder.
For SQL Server 2012 default location is C:\Program Files\Microsoft SQL Server\110\COM\
For SQL Server 2008/2008 R2 default location is C:\Program Files\Microsoft SQL Server\100\COM\.

By default, tablediff is designed to compare 2 tables at time and will generate one .sql script file, and there is no way to do an automatic comparison on all tables between 2 databases with tablediff. To do that, I decided to create PowerShell script to run tablediff on all tables in database.

I use PowerShell version 3, with imported SQL Server 2012 SQLPS module. To demonstrate solution let’s create 2 databases with 2 tables (source and destination databases must have the same tables names).

CREATE DATABASE SourceDB;
GO
CREATE DATABASE DestinationDB;
GO
USE SourceDB;
GO
CREATE TABLE dbo.CompareTable (col1 int identity(1,1),col2 varchar(10))
GO
INSERT INTO dbo.CompareTable (col2)
SELECT 'test' UNION ALL
SELECT 'test1' UNION ALL
SELECT 'test2'
GO
CREATE TABLE dbo.CompareTable1 (col1 int identity(1,1),col2 varchar(10))
GO
INSERT INTO dbo.CompareTable1 (col2)
SELECT 'test' UNION ALL
SELECT 'test1' UNION ALL
SELECT 'test2'
GO
USE DestinationDB
GO
CREATE TABLE dbo.CompareTable(col1 int identity(1,1),col2 varchar(10))
GO
INSERT INTO dbo.CompareTable (col2)
SELECT 'test1' UNION ALL
SELECT 'test2'
GO
CREATE TABLE dbo.CompareTable1 (col1 int identity(1,1),col2 varchar(10))
GO
INSERT INTO dbo.CompareTable1 (col2)
SELECT 'test' UNION ALL
SELECT 'test1' UNION ALL
SELECT 'test2'
GO
SELECT * FROM SourceDB.dbo.CompareTable
SELECT * FROM SourceDB.dbo.CompareTable1
SELECT * FROM DestinationDB.dbo.CompareTable
SELECT * FROM DestinationDB.dbo.CompareTable1
GO
/*
SourceDB.dbo.CompareTable
col1        col2
----------- ----------
1           test
2           test1
3           test2
SourceDB.dbo.CompareTable1
col1        col2
----------- ----------
1           test
2           test1
3           test2
DestinationDB.dbo.CompareTable
col1        col2
----------- ----------
1           test1
2           test2
DestinationDB.dbo.CompareTable1
col1        col2
----------- ----------
1           test
2           test1
3           test2
*/

Now we have 2 databases with 2 tables and sample data. We see that data in CompareTable of SourceDB and DestinationDB aren’t the same.
To compare data in these tables with PowerShell we can run. (Before you run the command change the SourceServer and DestinationServer)

& "C:\Program Files\Microsoft SQL Server\110\COM\tablediff.exe" -SourceServer SERVER\SQL2012 -SourceDatabase SourceDB -sourcetable CompareTable -DestinationServer SERVER\SQL2012 -DestinationDatabase DestinationDB  -destinationtable CompareTable -c -f D:\1\Script

We see 3 differences from result and Script.sql file is created to fix differences.
Content of Script.sql is:

 That is simple example how to run tablediff to compare data in 2 tables and to generate .sql script to fix differences.

To compare data in all tables we need to loop through all tables in SourceDB and run tablediff for all tables. For every table we also need to set new file name for the T-SQL script file. (If file name specified in -f parameter exists tablediff will fail with error “The file %s already exists.”).

Here is the script:

# Date:     25/02/2013
# Author:   Ana Mihalj
# Description:  PS script to execute tablediff to compere and find all differences in data between 2 databases.
# Version:  1.0
# Example Execution: (with default parameters) .\TableDiffWithParam.ps1
# or with non-default parameters
# .\TableDiffWithParam.ps1 -SourceServer SERVER\INSTANCE -SourceDatabase SourceDB -DestinationServer SERVER\INSTANCE -DestinationDatabase DestinationDB -OutputFolder D:\Folder

param( [string]$SourceServer = "SERVER\INSTANCE",
[string]$SourceDatabase = "SourceDB",
[string]$DestinationServer = "SERVER\INSTANCE",
[string]$DestinationDatabase = "DestinationDB",
[string]$OutputFolder = "D:\Folder"
)
#set path to tablediff utility
$tablediff = "C:\Program Files\Microsoft SQL Server\110\COM\tablediff.exe"

#get tables for compare
if ($SourceServer.Contains("\") -eq "True")
{ $Tables = Get-ChildItem SQLSERVER:\SQL\$SourceServer\Databases\$SourceDatabase\Tables\ | SELECT name }
else
{ $Tables = Get-ChildItem SQLSERVER:\SQL\$SourceServer\Default\Databases\$SourceDatabase\Tables\ | SELECT name }

#create output folder if it does not exist
if ((Test-Path $OutputFolder) -eq $false)
{
md $OutputFolder
}

#Output file
$OutputFile = $OutputFolder+"\Output.txt"

#for each table
foreach($table in $Tables)
{
#create new file name for the Transact-SQL script file
$DiffScript = $OutputFolder+"\"+$table.Name+".sql"

#If file exists throw the errror.
if ((Test-Path $DiffScript) -eq $true)
{
throw "The file " + $DiffScript + " already exists."
}

#execute tablediff
& $tablediff -sourceserver $SourceServer -sourcedatabase $SourceDatabase -sourcetable $table.Name -destinationserver $DestinationServer -destinationdatabase $DestinationDatabase -destinationtable $table.Name -strict -c -o $OutputFile -f $DiffScript

# check the return value and throw an exception if needed
# tablediff return values: 0 - Success, 1 - Critical error, 2 - Table differences
if ($LastExitCode -eq 1)
{
throw "Error on table " + $table.Name + " with exit code $LastExitCode"
}
}

Before you run the script you need to change parameters value and the path to tablediff location (if your installation isn’t SQL Server 2012 on default installation folder).
I hope you’ll find this script useful.