Automating SQL Compare Snapshots using SQL Snapper
If you have SQL Compare, then the SQL Snapper utility is very valuable 'extra' for certain team activities, because it can be freely distributed. It means that any developer can create a SQL Compare snapshot from databases that are on their local workstation and store them on the network.
Hidden in the distribution of Redgate’s SQL Compare is an application called SQL Snapper that performs the simple task of making a SQL Compare Snapshot of the metadata of a live database. You can create these snapshots directly using SQL Compare, of course, but SQL Snapper can be freely distributed. This means that it is easy to automate on every machine, so that even local databases that can’t be accessed across the network can be the source of a snapshot, and the database metadata can be encapsulated in a single file for copying deploying or archiving a database. It can, in effect, provide the basis of a deployment, the ‘release artefact’.
SQL Compare can use the snapper file as a source or as a target, as if it were a real database, or a scripts directory. When a snapshot is the target in a comparison, SQL Compare produces a script that would change the database represented by snapshot to be the same as the database represented by the source. This allows you to deploy changes even to disconnected databases, just by executing the auto-generated migration script that SQL Compare produces.
There are plenty of other uses for snapper files too. You can use one as a ‘reference’ of a particular database version, telling you quickly what was in it, and allowing you to quickly re-create it. You can also use one as a definition of a release and so use it to check for ‘drift’ and investigate what schema changes have been made. A large team can all save their work as snapshots as a precaution and have a DBA with SQL Compare restore everything in the case of a disaster. A deployment that uses SQL Compare as a release tool can use a snapper file for an emergency rollback, unless there are radical changes to tables.
What is a ‘SQL Compare Snapshot’?
Since SQL Compare was first written, SQL Server 2005 named a rather different object a ‘database snapshot’ which has caused some confusion. Whereas a SQL Server database snapshot contains the data as well, a SQL Compare Snapshot is merely a file containing a machine representation of the metadata of a database, as produced by a parser. It does not contain table data.
A SQL Compare Snapshot cannot be modified so, like a holiday snap, it captures a specific point in time. It provides a stable view of the database structure, as it existed when it was created. The snapshot is saved as a read-only binary file with the ‘snp‘ extension. It is basically an opaque proprietary BLOB and has no useful life independently of SQL Compare. These snapshot files are generally used to compare a database with a saved snapshot to see what changes have been made. If necessary, these changes can then be reverted by using the snapshot as the source for a deployment, as if you had a database ‘undo’ button.
You don’t need SQL Snapper to make a snapshot file. The File > Create Snapshot menu item in SQL Compare lets you create a snapshot from a database, backup, scripts folder, SQL Source Control project, SQL Change Automation project, or another snapshot, and choose where you want to store the resulting snp file. Alternatively, simply select a snapshot as a source for a comparison and hit the ‘Create …‘ hyperlink that appears.
Additionally, you can, on the Configure Backup page of the Deployment wizard, select the option to create a snapshot automatically before deployment, thereby providing a way of rolling back a deployment.
Wow, so I don’t need a scripts directory!!
Not so fast! A scripts directory allows you to use the power of your source control system to tell you who changed what and when. It also has extra magic that allows you to specify enumeration data, pre-deployment scripts, and post-deployment scripts. Think of SQL Snapper as being more like a ‘reference’ of a database build, or the means of providing an ‘undo’ button, though you’d still need SQL Compare to work the magic. I like to keep a copy of every release of a database as a snapshot, because I can easily produce a migration script to change a copy of the database from one version to another.
Using SQL Snapper interactively
You will find RedGate.SQLSnapper.exe in your SQL Compare installation directory (something like C:\Program Files (x86)\Red Gate\SQL Compare xx, where xx is your current version of SQL Compare).
SQL Snapper has its own interactive GUI, though you’d probably be more likely to use it from a script.
How do I distribute SQL Snapper?
If you need to use SQL Snapper on a machine that doesn’t or can’t have SQL Compare installed, you just need to copy the RedGate.SQLSnapper.exe executable from a computer on which SQL Compare is installed, along with all the DLL files from the same directory.
Getting Started with Automating SQL Snapper
Running snapper from PowerShell isn’t hard. Basically, if you’re lucky and have Windows authentication for your servers, and you aren’t too bothered about understanding errors, you can do this (the actual alias depends on your current version of SQL Compare):
1 2 3 |
Set-Alias Snapper "${env:ProgramFiles(x86)}\Red Gate\SQL Compare 14\Redgate.SQLSnapper.exe" Snapper /server:MyServerOrInstance /database:MyDatabase /MakeSnapshot:"$env:HOMEDRIVE$env:HOMEPATH\documents\MyDatabase.snp" if ($?) { "successfully produced snapshot of MyDatabase" } |
Or, if you need SQL Server authentication:
1 2 3 |
Set-Alias Snapper "${env:ProgramFiles(x86)}\Red Gate\SQL Compare 14\Redgate.SQLSnapper.exe" Snapper /server:MyServerOrInstance /database:MyDatabase /MakeSnapshot:"$env:HOMEDRIVE$env:HOMEPATH\documents\MyDatabase.snp" /username:MyUserName /password:MySecretPassword if ($?) { "successfully produced snapshot of MyDatabase" } |
Producing a snapshot of a single database
Here is a more usable script for running Snapper from PowerShell. I’ve dealt with error handling and the occasional requirement for usernames and passwords.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
Set-Alias Snapper "${env:ProgramFiles(x86)}\Red Gate\SQL Compare 14\Redgate.SQLSnapper.exe" $ErrorMeanings = #all the possible snapper errors. @{ "0" = "Success"; "1" = "General error"; "3" = "Illegal argument duplication" ; "8" = "Unsatisfied argument dependency";; "32" = "Value out of range" ; "33" = "Value overflow"; "34" = "Invalid value"; "64" = "Command line usage error" ; "65" = "Data error"; "69" = "Resource unavailable"; "70" = "An unhandled exception occurred" ; "73" = "Failed to create report"; "74" = "I/O error"; "77" = "Insufficient permission" ; "126" = "SQL Server error"; "130" = "Ctrl-Break"; } #to get help with the CLI for Snapper try # snapper /verbose /? ,#-----We need to fill in these four essential parameters ------ $SourceServer = 'MyServerOrInstance' # the name of the server or instance $SourceDatabase = 'MyDatabase' # the name of the database $username='MyUserName' # leave blank if Windows Authentication # and finally the name of the database $snapshotLocation = "$env:HOMEDRIVE$env:HOMEPATH\documents\$SourceDatabase.snp" <# Snapper over-writes existing Snapshot file #> <# we'll do splatting because this makes it easier to add credentials when necessary #> $AllArgs = @{ 'server' = "$SourceServer"; 'Database' = "$SourceDatabase"; 'makesnapshot' = "$snapshotLocation" } if ($username -ne '') #then it is using SQL Server Credentials { # we see if we've got these stored already $SqlEncryptedPasswordFile = "$env:USERPROFILE\$($username)-$SourceServer.xml" # test to see if we know about the password in a secure string stored in the user area if (Test-Path -path $SqlEncryptedPasswordFile -PathType leaf) { #has already got this set for this login so fetch it $SqlCredentials = Import-CliXml $SqlEncryptedPasswordFile } else #then we have to ask the user for it (once only) { # hasn't got this set for this login $SqlCredentials = get-credential -Credential $UserName # Save in the user area $SqlCredentials | Export-CliXml -Path $SqlEncryptedPasswordFile <# Export-Clixml only exports encrypted credentials on Windows. otherwise it just offers some obfuscation but does not provide encryption. #> } #now add the credentials to SQL Snapper $AllArgs += @{ 'username' = "$($SqlCredentials.UserName)"; 'password' = "$($SqlCredentials.GetNetworkCredential().password)" } } Snapper @allArgs if ($?) { "successfully produced snapshot of $SourceServer.$SourceDatabase in $snapshotLocation " } else { #if there was an error of some sort $SoFarSoGood = $false; $SQLCompareError = "SQK Snapper had an error creating snapshot of $SourceServer.$SourceDatabase in $snapshotLocation ! (code $LASTEXITCODE) - $( $ErrorMeanings."$lastexitcode")" Write-warning $SQLCompareError } |
Running Snapper in a batch file
It is perfectly possible to use a batch file to run SQL Snapper if you have an environment that doesn’t encourage PowerShell scripting, or forbids it for security reasons.
1 |
"%ProgramFiles(x86)%\\Red Gate\SQL Compare 14\Redgate.SQLSnapper.exe" /server:%sourceServer% /database:%SourceDatabase% %SourceSQLSnapperCredentials% /Makesnapshot:%SnapShotPath% |
You would, of course, need to assign values to all those variables. As I dislike having user IDs or passwords in scripts, I store these in the user area. This makes scripting a bit more complicated and so the following example might seem a little over-engineered
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
@echo off VERIFY errors 2>nul SETLOCAL ENABLEDELAYEDEXPANSION SETLOCAL ENABLEEXTENSIONS Set outcome=did our best if ERRORLEVEL 1 ( echo could not set local execution environment goto bombsite ) Rem set Source to the database you wish to take a snapshot of Set SourceDatabase=MyDatabase Rem Set SourceServer to the name of the server or instance containing this database Set SourceServer=MyServerOrInstance Rem Specify where you would like to save the snapshot file Set SnapShotPath="%HOMEDRIVE%%HOMEPATH%\documents\%SourceDatabase%.snp" Rem We now to check if you have provided credentials REM before you start. You need to write out your SQLCMD credentials to a file in your user rem area using code like this, ONLY if you use SQL Server Credentials. Rem so uncomment these next six lines if you need credentials and fill in your server credentials Rem echo MyUserID/MyPassword>%userProfile%\%SourceServer:\=_%SQLSnapper.txt Rem if ERRORLEVEL 1 ( Rem echo Could not write Source Credentials REM goto bombsite REM ) rem REM read in your Source SQLCMD command and credentials if you have any if exist %userProfile%\%SourceServer:\=_%SQLSnapper.txt ( Set /p SourceCredentials=<%userProfile%\%SourceServer:\=_%SQLSnapper.txt if ERRORLEVEL 1 ( echo Could not read in Source Credentials goto bombsite ) ) Rem Parse the source credentials into two variables set "Sourceuid=%SourceCredentials:/=" & set "SourcePw=%" REM credentials are presented in two different ways by the CLI apps Set SourcesqlcmdCredentials= /U %Sourceuid% /P %SourcePw% Set SourceSQLSnapperCredentials= /username:%Sourceuid% /Password:%SourcePw% REM Set source credentials correctly for windows security IF NOT DEFINED SourceCredentials ( Set SourcesqlcmdCredentials = set SourceSQLSnapperCredentials = ) echo Creating Snapshot from database %SourceDatabase% on %sourceServer% as %Source "%ProgramFiles(x86)%\\Red Gate\SQL Compare 14\Redgate.SQLSnapper.exe" /server:%sourceServer% /database:%SourceDatabase% %SourceSQLSnapperCredentials% /Makesnapshot:%SnapShotPath% SET outcome=created snapshot %SnapShotPath% from %SourceDatabase% on %sourceServer% if ERRORLEVEL 1 ( echo Could not create snapshot goto bombsite ) goto end :bombsite REM This is where the program goes if a problem is detected color 04 Echo Sadly, we failed, though we %outcome%! echo on ENDLOCAL color Exit /b 1 :end REM This is where we go if all ended well. Echo we %outcome% and finished successfully ENDLOCAL echo on Exit /b 0 |
Doing a snapshot of every database on the server as a batch
It is when you want to do every database on a server that this more engineered approach to the batch file becomes more worthwhile. Using the same basic structure, we can loop through every database on the server (excluding the system databases and utility databases that you specify, of course):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 |
rem @echo off VERIFY errors 2>nul SETLOCAL ENABLEDELAYEDEXPANSION SETLOCAL ENABLEEXTENSIONS Set outcome=did our best if ERRORLEVEL 1 ( echo could not set local execution environment goto bombsite ) Rem Set SourceServer to the name of the server or instance containing these databases Set SourceServer=MyServerOrInstance Rem Specify where you would like to save the snapshot file Set SnapShotPath=%HOMEDRIVE%%HOMEPATH%\documents\ Rem We now to check if you have provided credentials REM before you start, You need to write out your SQLCMD credentials to a file in your user rem area using code like this, ONLY if you use SQL Server Credentials. Rem so uncomment these next five or six lines if you need credentials and fill in your server credentials rem echo MyUserName/MyPassword>%userProfile%\%SourceServer:\=_%SQLSnapper.txt rem if ERRORLEVEL 1 ( rem echo Could not write Source Credentials rem goto bombsite rem ) rem REM read in your Source SQLCMD command and credentials if you have any if exist %userProfile%\%SourceServer:\=_%SQLSnapper.txt ( Set /p SourceCredentials=<%userProfile%\%SourceServer:\=_%SQLSnapper.txt if ERRORLEVEL 1 ( echo Could not read in Source Credentials goto bombsite ) ) Rem Parse the source credentials into two variables set "Sourceuid=%SourceCredentials:/=" & set "SourcePw=%" Rem credentials are presented in two different ways by the CLI apps Set SourcesqlcmdCredentials= /U %Sourceuid% /P %SourcePw% Set SourceSQLSnapperCredentials= /username:%Sourceuid% /Password:%SourcePw% rem set source credentials correctly for windows security IF NOT DEFINED SourceCredentials ( Set SourcesqlcmdCredentials = set SourceSQLSnapperCredentials = ) Set QUERY="set nocount on; Select name from sys.databases where name not in ('master','tempdb','model','msdb');" for /F usebackq %%i in (`sqlcmd -S %sourceServer% %SourcesqlcmdCredentials% -d master -h -1 -f 65001 -Q %QUERY%`) do ( echo %%i REM catch the first error caused by the sqlcmd if ERRORLEVEL 1 ( echo An error occured while accessing %SourceServer% to get the list of databases goto bombsite ) Rem set Source to the database you wish to take a snapshot of echo Creating Snapshot from database %%i on %sourceServer% as %Source "%ProgramFiles(x86)%\\Red Gate\SQL Compare 14\Redgate.SQLSnapper.exe" /server:%sourceServer% /database:%%i %SourceSQLSnapperCredentials% /Makesnapshot:%SnapShotPath%%%i.snp SET outcome= snapshot %SnapShotPath%%SourceDatabase% from %%i on %sourceServer% if ERRORLEVEL 1 ( echo Could not create snapshot %%i on %sourceServer% goto bombsite ) ) goto end :bombsite Rem This is where the program goes if a problem is detected color 04 Echo Sadly, we failed, while creating %outcome%! echo on ENDLOCAL color Exit /b 1 :end REM This is where we go if all ended well. Echo we created all our database snapshots and finished successfully ENDLOCAL echo on Exit /b 0 |
Doing a snapshot of every database in the server in PowerShell
Just so show how easy it is, here is the same routine done in PowerShell. It does snapshots of all the databases on the server, excluding the obvious system databases such as tempdb
and master
, and any others you list.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
Set-Alias Snapper "${env:ProgramFiles(x86)}\Red Gate\SQL Compare 14\Redgate.SQLSnapper.exe" $ErrorMeanings = #all the possible snapper errors. @{ "0" = "Success"; "1" = "General error"; "3" = "Illegal argument duplication" ; "8" = "Unsatisfied argument dependency";; "32" = "Value out of range" ; "33" = "Value overflow"; "34" = "Invalid value"; "64" = "Command line usage error" ; "65" = "Data error"; "69" = "Resource unavailable"; "70" = "An unhandled exception occurred" ; "73" = "Failed to create report"; "74" = "I/O error"; "77" = "Insufficient permission" ; "126" = "SQL Server error"; "130" = "Ctrl-Break"; } #to get help with the CLI for Snapper try # snapper /verbose /? ,#-----We need to fill in these four essential parameters ------ $SourceServer = 'MyServerOrInstance' # the name of the server or instance $username = 'MyUserName' # leave blank if Windows Authentication # and finally the name of the database $snapshotLocation = "$env:HOMEDRIVE$env:HOMEPATH\documents\" <# Snapper over-writes existing Snapshot file #> $SQLServerLoginForSnapper = @{ } if ($username -ne '') #then it is using SQL Server Credentials { # we see if we've got these stored already $SqlEncryptedPasswordFile = "$env:USERPROFILE\$($username)-$SourceServer.xml" # test to see if we know about the password in a secure string stored in the user area if (Test-Path -path $SqlEncryptedPasswordFile -PathType leaf) { #has already got this set for this login so fetch it $SqlCredentials = Import-CliXml $SqlEncryptedPasswordFile } else #then we have to ask the user for it (once only) { # hasn't got this set for this login $SqlCredentials = get-credential -Credential $UserName # Save in the user area $SqlCredentials | Export-CliXml -Path $SqlEncryptedPasswordFile <# Export-Clixml only exports encrypted credentials on Windows. otherwise it just offers some obfuscation but does not provide encryption. #> } $Uid = $SqlCredentials.UserName; $Pwd = $SqlCredentials.GetNetworkCredential().password #now add the credentials to SQL Snapper $SQLServerLoginForSnapper = @{ 'username' = $Uid; 'password' = $Pwd; } } $query = "set nocount on; Select name from sys.databases where name not in ('master','tempdb','model','msdb');" if ($username -ne '') { $databases = sqlcmd -S "$SourceServer" -U $Uid -P $Pwd -d master -h -1 -f 65001 -Q $query; } else { $databases = sqlcmd -S "$SourceServer" -d master -h -1 -f 65001 -Q $query; } $databases | Foreach{ $ThisDatabase = $_.Trim() <# we'll do splatting because this makes it easier to add credentials when necessary #> $AllArgs = @{ 'server' = "$SourceServer"; 'Database' = $ThisDatabase; 'makesnapshot' = "$snapshotLocation\$ThisDatabase.snp" } $AllArgs += $SQLServerLoginForSnapper Snapper @allArgs if ($?) { "successfully produced snapshot of $SourceServer.$ThisDatabase in $snapshotLocation" } else { #if there was an error of some sort $SoFarSoGood = $false; $SQLCompareError = "SQK Snapper had an error creating snapshot of $SourceServer.$ThisDatabase in $snapshotLocation ! (code $LASTEXITCODE) - $( $ErrorMeanings."$lastexitcode")" Write-warning $SQLCompareError } } |
Conclusions
Everything one can do in Snapper can be done using SQL Compare. If that worries you, you are missing the point. It is unlikely that all developers will have SQL Compare installed on their machines. They can have SQL Snapper though, so they can easily create SQL Compare snapshots for SQL Server databases that are on local servers on their workstations and store them on the network. Every server can have SQL Snapper on it so that an automatic process can take snapshots of databases. If an issue crops up, it is easy to use SQL Compare to create scripts to migrate between databases represented by source and target snapshots, so you can end up with a system that can roll back an unfortunate change or can quickly provide databases at a particular revision.
It is an open-ended safety belt that allows the database developer to feel safer to experiment, because nothing is likely to get lost.