Product articles SQL Compare Database Builds and Deployments
Automating SQL Compare Snapshots using…

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.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

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):

Or, if you need SQL Server authentication:

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.

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.

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

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):

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.

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.

Tools in this post

SQL Compare

Compare and synchronize SQL Server database schemas

Find out more