Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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}"
- }
- }
Add Comment
Please, Sign In to add comment