Skip to content

Instantly share code, notes, and snippets.

@LukeCarrier
Created November 28, 2018 12:03
Show Gist options
  • Save LukeCarrier/f2141bdc6a7b1e7349bbb1e85c0d26ae to your computer and use it in GitHub Desktop.
Save LukeCarrier/f2141bdc6a7b1e7349bbb1e85c0d26ae to your computer and use it in GitHub Desktop.
Diff your SQL Server databases
Import-Module -Name SqlServer
function Import-AvadoSqlServerManagementObjects()
{
$assemblies = @(
"Microsoft.SqlServer.Management.Common",
"Microsoft.SqlServer.Smo",
"Microsoft.SqlServer.Dmf ",
"Microsoft.SqlServer.Instapi ",
"Microsoft.SqlServer.SqlWmiManagement ",
"Microsoft.SqlServer.ConnectionInfo ",
"Microsoft.SqlServer.SmoExtended ",
"Microsoft.SqlServer.SqlTDiagM ",
"Microsoft.SqlServer.SString ",
"Microsoft.SqlServer.Management.RegisteredServers ",
"Microsoft.SqlServer.Management.Sdk.Sfc ",
"Microsoft.SqlServer.SqlEnum ",
"Microsoft.SqlServer.RegSvrEnum ",
"Microsoft.SqlServer.WmiEnum ",
"Microsoft.SqlServer.ServiceBrokerEnum ",
"Microsoft.SqlServer.ConnectionInfoExtended ",
"Microsoft.SqlServer.Management.Collector ",
"Microsoft.SqlServer.Management.CollectorEnum",
"Microsoft.SqlServer.Management.Dac",
"Microsoft.SqlServer.Management.DacEnum",
"Microsoft.SqlServer.Management.Utility"
)
foreach ($assembly in $assemblies)
{
[Reflection.Assembly]::LoadWithPartialName($assembly) | Out-Null
}
}
class AvadoDatabase
{
[string] $Server
[string] $Login
[string] $Password
[string] $Database
[string] $Schema
AvadoDatabase([string] $Server, [string] $Login, [string] $Password,
[string] $Database, [string] $Schema)
{
$this.Server = $Server
$this.Login = $Login
$this.Password = $Password
$this.Database = $Database
$this.Schema = $Schema
}
[string] ToString()
{
return "schema $($this.Database).$($this.Schema) on $($this.Server) as $($this.Login)"
}
}
function Export-AvadoDatabaseSchemaDacpac()
{
[CmdletBinding()]
param(
[Parameter(Mandatory=$true)]
[string] $SqlPackagePath,
[Parameter(Mandatory=$true)]
[AvadoDatabase] $Source,
[Parameter(Mandatory=$true)]
[string] $TargetFile
)
& $SqlPackagePath `
/Action:Extract `
"/SourceServerName:$($Source.Server)" `
"/SourceUser:$($Source.Login)" `
"/SourcePassword:$($Source.Password)" `
"/SourceDatabaseName:$($Source.Database)" `
"/TargetFile:${TargetFile}"
}
function Verify-AvadoDatabaseIntegrity()
{
[CmdletBinding()]
param(
[Parameter(Mandatory=$true)]
[string] $SqlPackagePath,
[Parameter(Mandatory=$true)]
[string] $TableDiffPath,
[Parameter(Mandatory=$true)]
[AvadoDatabase] $Source,
[Parameter(Mandatory=$true)]
[AvadoDatabase] $Destination,
[Parameter(Mandatory=$true)]
[string] $OutputDirectory
)
Write-Verbose "Using SqlPackage ${SqlPackagePath}"
Write-Verbose "Using tablediff ${TableDiffPath}"
Write-Verbose "Using source $($Source.ToString())"
Write-Verbose "Using destination $($Destination.ToString())"
Write-Verbose "Writing results to ${OutputDirectory}"
Write-Host "Ensuring output directory is accessible"
$outputDirectoryExists = Test-Path -Path $OutputDirectory -PathType Container
if (!$outputDirectoryExists)
{
Write-Verbose "Creating missing output directory ${OutputDirectory}"
New-Item -Path $OutputDirectory -ItemType Directory -Force | Out-Null
}
if ($null -ne (Get-ChildItem -Path $OutputDirectory))
{
throw "Output directory ${OutputDirectory} already contained files"
}
Write-Host "Connecting to source server..." -NoNewline
$sourceConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection `
$Source.Server
# Authentication mode
# $false for SQL Server
# $true for Windows
$sourceConnection.LoginSecure = $false
$sourceConnection.Login = $Source.Login
$sourceConnection.Password = $Source.Password
$sourceServer = New-Object Microsoft.SqlServer.Management.Smo.Server $sourceConnection
Write-Host "done"
Write-Host "Enumerating source database tables..." -NoNewline
$sourceDatabase = $sourceServer.Databases.Item($source.Database)
$tablesSql = @"
select t.name
from $($Source.Database).sys.tables t
inner join $($Source.Database).sys.schemas s
on s.schema_id = t.schema_id
where t.type_desc = 'USER_TABLE'
and t.is_ms_shipped = 0
and s.name = '$($Source.Schema)'
"@
$tablesDataSet = $sourceDatabase.ExecuteWithResults($tablesSql)
$tables = $tablesDataSet.Tables.Rows | ForEach{ $_.Item("name") }
Write-Host "done; found $($tables.Count)"
$sourceDacpac = Join-Path $OutputDirectory "1_source.dacpac"
$destinationDacpac = Join-Path $OutputDirectory "1_destination.dacpac"
$schemaSql = Join-Path $OutputDirectory "2_schema.sql"
Write-Host "Extracting source schema..." -NoNewline
$elapsed = Measure-Command {
Export-AvadoDatabaseSchemaDacpac `
-SqlPackagePath $SqlPackagePath `
-Source $Source -TargetFile $sourceDacpac
}
Write-Host "done; took ${elapsed}"
Write-Host "Extracting destination schema..." -NoNewline
$elapsed = Measure-Command {
Export-AvadoDatabaseSchemaDacpac `
-SqlPackagePath $SqlPackagePath `
-Source $Destination -TargetFile $destinationDacpac
}
Write-Host "done; took ${elapsed}"
Write-Host "Comparing schema between source and destination..." -NoNewline
$elapsed = Measure-Command {
& $SqlPackagePath `
/Action:Script `
"/SourceFile:${sourceDacpac}" `
"/TargetFile:${destinationDacpac}" `
"/TargetDatabaseName:$($Destination.Database)" `
/OutputPath:$schemaSql
}
Write-Host "done; took ${elapsed}"
foreach ($table in $tables)
{
Write-Host "Comparing data in ${table}..." -NoNewline
$elapsed = Measure-Command {
& $TableDiffPath `
-sourceserver $Source.Server `
-sourceuser $Source.Login `
-sourcepassword $Source.Password `
-sourcedatabase $Source.Database `
-sourceschema $Source.Schema `
-destinationserver $Destination.Server `
-destinationuser $Destination.Login `
-destinationpassword $Destination.Password `
-destinationdatabase $Destination.Database `
-destinationschema $Destination.Schema `
-sourcetable $table `
-destinationtable $table `
-o (Join-Path $OutputDirectory "3_data_report.${table}.txt") `
-f (Join-Path $OutputDirectory "4_data_sql${table}.sql") `
| Out-Null
}
Write-Host "done; took ${elapsed}"
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment