What’s a SQL Compare Snapshot?

SQL Compare is a core product from Redgate and I’ve got a series on some of the interesting things I’ve found. Download a trial today if you haven’t tried it.


I saw a short video from Grant Fritchey on the Redgate channel that shows how to cerate a snapshot in SQL Compare. I think snapshots are great, and there are lots of uses (more on that later), but I do find sometimes I use the term and people are confused.


This is one of those overloaded terms. We have SAN snapshots, backup snapshots, database snapshots, and more that many of us deal with. All of these really mean a point in time view of that particular data set (image, machine, disk, database, etc.).

In SQL Server, we’ve had DB snapshots, which create a second database that is a copy as of a point in time of the original There are lots of restrictions on these, but they are useful for quick rollbacks during deployments, or point in time reporting, etc. Useful little creatures.

SQL Compare Snapshots

Many of you know SQL Compare as a tool that looks at two databases, gives you the differences in all objects, allows filtering, and also builds a deployment script to make the target database, DatabaseB, look like the source database, DatabaseA. In other words, if I add a table and view to the Source, and compare this with a destination, I’d see the new table and view as differences that I need to deploy.

That’s great when working with databases, however sometimes I don’t have control over a database and can’t be sure that it has a stable codebase (no changes). SQL Compare snapshots let me copy over the state of the database into a folder.

If I grab a random database and make a snapshot, the database looks like this:

2017-09-14 18_22_31-SQLQuery1.sql - (local)_SQL2016.sandbox (PLATO_Steve (72))_ - Microsoft SQL Serv

and the snapshot looks like this:

2017-09-14 18_23_24-Snapshots

Not quite the same. However, all the code is in the snapshot. It’s a binary file with all the code. If I use this as a target in SQL Compare:

2017-09-14 18_24_14-New project_

I’ll see this if I haven’t changed the database.

2017-09-14 18_25_17-SQL Compare - New project_

Why Use Snapshots?

One of the main reasons I like to use snapshots is there are a stable view of code, like a tag or branch in a VCS, but they aren’t modified after created. They are a great way for me to capture the state of my code after a deployment (or before).

Later I can compare my snapshot with the database and detect changes. This is great for detecting production drift where someone might change production and I’m unaware.

I’m sure you could come up with other uses for a point in time view of your code.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.
%d bloggers like this: