Compare tables with Powershell

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

2 thoughts on “Compare tables with Powershell

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

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

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