SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

What’s a SQL Compare Snapshot?

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.

Filed under: Blog Tagged: Redgate, SQL Compare, syndicated

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...