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
$diff

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
		continue;
	}
	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
	$diff
}
Advertisements

2 Comments 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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: