30 April 2018
30 April 2018

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.

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.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    Eliminating 'noise' from database deployment scripts using SQL Compare project options

    SQL Compare has project options that allow you to ignore certain objects, or certain differences between objects, as well as deal with more complicated object differences, such as different column orders in a table in the source.

  • Webinar

    How to treat compliance in DevOps as serious business

    We hosted this webinar to discuss how, by embedding ‘Privacy by Design’ in your Database DevOps practices, you can introduce a framework that builds a bridge between fast AND secure software development. We arm you with practical tips on how to discover, classify, protect and monitor your SQL Server estate, and therefore ensure that your Database DevOps practices are secure

  • Article

    How to automate database synchronization using the SQL Compare command line

    Demonstrating a one-click way to compare a source database to a target database, or a snapshot of it, and generate a script to synchronize them, as well as documentation describing the changes.

  • Article

    How to version control a database using GitHub and SQL Source Control

    This article explains some simple steps to create a GitHub database repository and then version control your SQL Server database, for team development work, using SQL Source Control.

  • University

    Take the SQL Comparison Tools course

    This course takes you from installation all the way up to getting the most out of the industry standard tools for SQL Schema & Data comparison. Whether you need to deploy changes or explore the differences between your environments, through this course you'll learn how SQL Compare and SQL Data Compare can help you do the job quickly, simply, and with zero errors.

  • Forums

    SQL Compare Forum

    Forum for users of SQL Compare schema synchronization utility