2011-10-13

Red-Gate SQL Compare with PowerShell

I would like to compare the structure of two databases as in development vs production.
The tool SQL Compare from Red-Gate I have used on a daily basis, and I want similar result without the GUI, so that I can automate a comparison.
In the SQL Toolbelt there are beside SQL Compare a SQL Comparison SDK, but it is a version behind SQL Compare. One thing I would miss is the possibility to compare with a backup or a scriptfile.
By looking into the C# examples of the SDK and the assemblies with Visual Studio Object Browser, this (very) simple example gives a usable result. Please consider the example as a technical sprint, not a final solution.

# Generate path for Red-Gate SQL Compare assemblies
$SqlComparePath = "\Red Gate\SQL Compare 9"
# True if the OS is 64 bit (x64)
if([IntPtr]::Size -eq 8) { $SqlComparePath = ${env:ProgramFiles(x86)} + $SqlComparePath }
else { $SqlComparePath = ${env:ProgramFiles} + $SqlComparePath }

# Add Red-Gate Compare assembly
Add-Type -Path "$SqlComparePath\RedGate.SQLCompare.Engine.dll"

# Register staging database
[string]$stagingServerName = 'SQLDEV42.sqladmin.lan'
[string]$stagingDbName = 'sqladmin_repository'
$stagingConnectionProperties = New-Object RedGate.SQLCompare.Engine.ConnectionProperties $stagingServerName, $stagingDbName
$stagingDb = New-Object RedGate.SQLCompare.Engine.Database
$stagingDb.Register( $stagingConnectionProperties, [RedGate.SQLCompare.Engine.Options]::Default )

# Register Production database
[string]$prodServerName = 'SQLPRD42.sqladmin.lan'
[string]$prodDbName = 'sqladmin_repository'
$prodConnectionProperties = New-Object RedGate.SQLCompare.Engine.ConnectionProperties $prodServerName, $prodDbName
$prodDb = New-Object RedGate.SQLCompare.Engine.Database
$prodDb.Register( $prodConnectionProperties, [RedGate.SQLCompare.Engine.Options]::Default )

# Compare databases. Result in RedGate.SQLCompare.Engine.Difference object
$stagingDb.CompareWith( $prodDb, [RedGate.SQLCompare.Engine.Options]::Default ) |
Format-Table -Property Type,DatabaseObjectType,Name -AutoSize


The result is something like this:
 Type DatabaseObjectType Name
 ---- ------------------ ----
Equal              Table [sqladmin].[collation]
Equal              Table [sqladmin].[computer]
Equal              Table [sqladmin].[database]
Equal              Table [sqladmin].[database_history]
Equal              Table [sqladmin].[department]
Equal              Table [sqladmin].[edition]
Equal              Table [sqladmin].[environment]
Equal              Table [sqladmin].[file]
Equal              Table [sqladmin].[filegroup]
Equal              Table [sqladmin].[security]
Equal              Table [sqladmin].[ssdb]
Equal              Table [sqladmin].[version]
Equal               Role public
Equal               Role sqlanchor_user
Equal               Role db_owner
Equal               Role db_accessadmin
Equal               Role db_securityadmin
Equal               Role db_ddladmin
Equal               Role db_backupoperator
Equal               Role db_datareader
Equal               Role db_datawriter
Equal               Role db_denydatareader
Equal               Role db_denydatawriter
Equal             Schema dbo
Equal             Schema guest
Equal             Schema INFORMATION_SCHEMA
Equal             Schema sys
Equal             Schema sqladmin
Equal             Schema sqlanchor
Equal             Schema db_owner
Equal             Schema db_accessadmin
Equal             Schema db_securityadmin
Equal             Schema db_ddladmin
Equal             Schema db_backupoperator
Equal             Schema db_datareader
Equal             Schema db_datawriter
Equal             Schema db_denydatareader
Equal             Schema db_denydatawriter
Equal               View [sqladmin].[v_computer]
Equal               View [sqladmin].[v_database]
Equal               View [sqladmin].[v_ssdb]
Equal               View [sqladmin].[v_ssdb_full]
Equal    StoredProcedure [sqlanchor].[ssdb_version-get]
Equal    StoredProcedure [sqlanchor].[environment-get]
Equal    StoredProcedure [sqlanchor].[environment_detail-get]
Equal    StoredProcedure [sqlanchor].[computer_summary-get]

...

Before you use the assemblies, take a look at the Red-Gate license agreement.