Archive

Archive for February, 2013

Compare tables between 2 databases with Tablediff and PowerShell

25/02/2013 Leave a comment

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

TableDiff

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

TableDiff_Script_sql

 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.

Advertisements

T-SQL Tuesday #39 – Set file permissions with PowerShell

12/02/2013 1 comment

TSQL2sday

Wayne Sheffield (blog|twitter) is hosting this month’s T-SQL Tuesday and the subject is “Can you shell what the PoSH is Cooking“. More specifically he want that we blog about anything PowerShell and SQL Server related.

As a DBA I use PowerShell for some automation tasks such as: reading error logs, scripting SQL server objects, comparing data in tables etc. Today I will write about script I wrote for setting read file permission on SQL Server trace files.

From SQL Server 2005 onwards, when you run server side trace in SQL server, a new trace file will be created and the permission for this trace file is set only to SQL Service account. The permissions of the directory are not inherited.

Because of that SQL Server security policy, I find myself in a situations that I have hundreds of trace files on the server share, but I cannot access them with my user. So I decided to write a PowerShell script to give my user read access to all trace files in specified folder.

$path = "D:\Folder\"
$user = "domain\username"
$SystemRights = "Read"
$AccessType = "Allow"
$AccessRule = New-Object System.Security.AccessControl.FileSystemAccessRule($user, $SystemRights, $AccessType)
$files = Get-ChildItem $path
foreach($file in $files)
{
$acl = (Get-Item $file).GetAccessControl("Access")
$acl.AddAccessRule($AccessRule)
$filepath = $path +""+ $file.name
Set-Acl $filepath $acl
} 
Categories: T-SQL Tuesday