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.
2011-10-13
Subscribe to:
Posts (Atom)