Skip to content

December 10, 2012


Compare tables with Powershell

by Scott Newman

Posting this for Michelle Uffort (@sqlfool).

$saUser = 'sa'
$saPwd = get-content "C:\Powershell\Monitoring\sacred.txt" | convertto-securestring 

$targetSrvCn = 'TargetServer'
$srcSrvCn = 'SourceServer'
$srcDBName = 'srcDB'
$targetDBName = 'TargetDB'

$targetConn = New-Object ('Microsoft.SqlServer.Management.Common.ServerConnection') ('TargetServer', $saUser, $saPwd)
$targetServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $targetConn
$targetDB = $targetServer.Databases | where {$_.Name -eq $targetDBName}
$targetTables = $targetDB.Tables | where{$_.Name -notlike "*QueueCacheData*"}

$sourceConn = New-Object ('Microsoft.SqlServer.Management.Common.ServerConnection') ('SourceServer', $saUser, $saPwd)
$sourceServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sourceConn
$sourceDB = $sourceServer.Databases | where {$_.Name -eq $srcDBName}
$sourceTables = $sourceDB.Tables | where{$_.Name -notlike "*QueueCacheData*"}

$diff = Compare-Object -DifferenceObject $targetTables -ReferenceObject $sourceTables -SyncWindow 300 -Property Name

foreach($table in $sourceTables)
	$targetTable = $targetTables | where{$_.Name -eq $table.Name}
	if($targetTable -eq $null)
		Write-Host "Table $($table.Name) does not exist in target database." -ForegroundColor Red
	write-host $table.Name $targetTable.Name
	$targetColumns = $targetTable.Columns | sort $_.Name
	$srcColumns = $table.Columns | sort $_.Name
	$diff = Compare-Object -ReferenceObject $srcColumns -DifferenceObject $targetColumns -Property Name -SyncWindow 100
Read more from Powershell, SQL Server
2 Comments Post a comment
  1. Neeraj
    Jan 4 2013

    Thanks for this useful script Scott.
    Encountered error at line 12 ($db.Tables) . I think it should be $targetDB.Tables .

  2. Jan 4 2013

    You are correct sir. Fixed.


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 )

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

Note: HTML is allowed. Your email address will never be published.

Subscribe to comments

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: