Product articles SQL Toolbelt Database Builds and Deployments
SQL Compare Snapshots: a lightweight…

SQL Compare Snapshots: a lightweight database version control and rollback mechanism

During the proof-of-concept phase of development work, SQL Compare Snapshots offer an easy way to work out what broke, if a change causes some tests to fail, as well as a simple ‘roll back’ technique to return quickly to the last working copy.

Guest post

This is a guest post from Feodor Georgiev. Feodor has worked with SQL Server for over 15 years, specializing in database architecture, Microsoft SQL Server data platform, data model design, database design, integration solutions, business intelligence, reporting, as well as performance optimization and systems scalability.

In the past 3 years, he has expanded his focus to coding in R for assignments relating to data analytics and data science. Outside of his day-to-day schedule, he blogs, shares tips on forums and writes articles on his Data Messenger blog.

I’m a big fan of using SQL Compare during proof-of-concept (POC) development. During the very early stages, I’m often unsure of the value of my current coding efforts and am not ready to commit unstable changes to a version control system (VCS). However, I do need a lightweight way to keep track of my changes. For this, I use SQL Compare to script my source database into an empty directory, together with a click-button way to synchronize the directory, so that it reflects my latest swathe of POC changes.

This article describes a simple extension to these techniques, incorporating SQL Compare Snapshots. A snapshot is a compact, binary file that captures a point-in-time copy of the structure of the source database. A snapshot does not contain any table data. It works a bit like a tag or branch in a VCS, but a snapshot can’t be modified, and so represents a completely stable view of the database structure, as it existed when it was created.

Even during a POC, I’ll develop simple tests to verify that my changes behave as I expect, under a range of conditions. When I reach a point where a POC passes my initial tests, I’ll save it as a SQL Compare Snapshot. This gives me an easy way to work out what I broke, if subsequent changes cause some tests to fail, as well as simple ‘roll back’ technique to return quickly to my last working copy.

Creating a new snapshot

To create a new SQL Compare Snapshot, open a new project, specify the Target as a Snapshot, and click Create….

Figure 1

The source for the new snapshot can be a database, a backup, another snapshot, a version control directly or a simple scripts folder. In this example, I use a database.

Specify the SQL Server instance (a SQL Server 2016 instance), the name of the database (Customers), and a name and location for the snapshot. By default, the name will be the same as the source database. However, you may wish to adapt this to reflect the current database version, for example. The location will be the current user’s Documents folder on the local machine.

C:\Users\TONY~1.DAV\AppData\Local\Temp\SNAGHTML2d7091.PNG

Figure 2

Having created a snapshot, we can use it as a target, as a source, or as both.

Using a snapshot as the source

When we use a snapshot as a source, SQL Compare will compare it to the target and generate a deployment script to synchronize the target so that it matches the state of the source snapshot. This has multiple uses.

Creating a scripts directory

If the source is a snapshot and the target is an empty database, or empty scripts folder, the resulting deployment script will create all the database objects, or all the database object scripts, as they exist in the snapshot.

It might be that your system administrator gives you a SQL Compare snapshot of the current production database, as the starting point for your POC development, for example if access to the production data is prohibited, or simply as a quick, lightweight alternative to a database backup, in cases where the course database is very large.

We can simply script the snapshot into an empty directory. I described this basic process in my previous article and the only difference here is that we set a snapshot as a source, rather than a database. Notice that the source lists the Customers database that was the source for the snapshot (taken from a SQL Server 21016 instance) rather than the snapshot itself.

Figure 3

After we have compared the snapshot to the empty script folder, we select the objects we are interested in deploying. In this case we deploy everything.

Figure 4

Finally, we specify how we would like to deploy the changes, either letting SQL Compare write the scripts directly into the folder or creating a deployment script for review.

Figure 5

This results in a directory of object scripts, that we can use as a lightweight versioning mechanism during POC work. We can build a new database from these scripts, fill it with any test data we need, make our required database changes, run tests, and then update the directory with our changes, by comparing the development database, as the source, to the script directory, as the target.

Notice in Figure 5 that we have an option to create a snapshot of the target before deployment. This provides a very useful ‘rollback’ mechanism, during POC work, and highly recommended during database releases generally, for ensuring there is a safe way to “reverse out” of the deployment, if required.

Generating a rollback script

If the source is a snapshot and the target is a newer version of the database, or of the underlying script directory, then the deployment script will modify the target so that its structural state matches that of the snapshot. In effect, this means it will roll back all changes, reverting any object modifications, deleting any objects that were subsequently added, and recreating any that were removed.

Let’s say I’ve deployed a new CustomersPOC database, from the scripts folder, to my development SQL Server 2017 instance, and then made some database changes to my development copy (dropping a table), and I now run a comparison with the original snapshot as the source.

Figure 6

Before deploying any changes to the target, we have the option to Backup target before deployment, and SQL Compare will either create a full database backup, which will back up the schema and all data, or create a new SQL Compare snapshot of the target, which will be a schema-only copy, saving both time and disk space. This is useful if I want to, for example, re-run certain tests on the previous POC, but don’t want to lose my subsequent work

Figure 7

Finally, we generate the deployment script which will, in this case, recreate in my target CustomerPOC database the Contacts table I previously dropped.

C:\Users\TONY~1.DAV\AppData\Local\Temp\SNAGHTML3f5d31.PNG

Figure 8

SQL Compare will first capture a new snapshot of the target (my CustomersPOC development database) and then run the deployment script to modify CustomersPOC, synchronizing with the original Customers snapshot.

Using a snapshot as the target

We’ve already one way using the snapshot as a target, when we wish to create a new snapshot from the current source, either at the start of the development project, or to save the state of the source database for later use.

However, we can also use an existing snapshot as a target and compare it to a database, backup, scripts folder or another snapshot. Remember that we cannot modify an existing snapshot, so the target of the resulting deployment script is, in fact, the source database for the snapshot. The deployment script will synchronize the target so that it is the same structurally as the source database.

This is useful when it approaches the time to deploy the development changes to pre-production environment, or to production. The team can, for example, compare the latest database version, in development, with a snapshot of the production database, and provide the DBA with the deployment script, for detailed review.

Figure 9

Using snapshots as both source and a target

Sometimes the team will want to compare two snapshots. This might happen if, for example there was no way to directly compare the source development database to the latest database version in a different environment, because they are on separate network segments. Alternatively, the development team might have several PoC snapshots and want to know the difference between them so that they can merge the changes into a single database.

With a snapshot set as both source and target, SQL Compare will generate a deployment script to modify the database for the target snapshot so that it matches structurally the source snapshot. So, for example, if the development team provides the DBA with a snapshot of the final version of the development database, the DBA could compare it to the latest snapshot of the production database, and examine the resulting deployment script.

Conclusion

Snapshots provide an easy way to keep track of changes during development work, whether they are small changes between iterations, or significant changes between releases. Snapshots can be used to quickly generate rollback scripts during releases even in environments which are isolated by infrastructure design for security reasons.

Tools in this post

SQL Compare

Compare and synchronize SQL Server database schemas

Find out more

SQL Toolbelt

The industry-standard tools for SQL Server development and deployment.

Find out more