Diff your SQL Server databases

diff-db.ps1

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}"
    }
}