Last month I wrote about how drift correction can be used to keep track of changes that have been made outside of your established change management process.

In that post I used the example of a DBA making a change directly to the Production environment. In this post we will look at how to re-sync your database to a different source control branch using the same approach.

What is “drift”?

Drift occurs when a target database deviates from the baseline that was used to originally deploy it (eg. the database project within your repository’s trunk).

This will typically occur if changes are made outside of source control, but can also happen if you modify source control itself by removing or otherwise changing the project migration scripts.

By switching the branch/tag underlying your build configuration (eg. within your Continuous Integration server), you can generate a script to synchronize from another tag/snapshot/branch in source control, effectively “correcting” the drift.


In the above example, the decision was made to roll back the Version 1.2 schema to the state defined by Version 1.1: the source control head was repositioned to the Version 1.1 tag, causing drift between the currently-deployed baseline (v1.2) and the canonical baseline (v1.1).

Changes supported

At the moment, the following types of changes can be synchronized using drift correction:

  • Deploy earlier versions of table-bound objects such as foreign keys, indexes, column constraints, etc.
  • Deploy earlier versions of programmable objects, such as stored procedures, views, triggers, etc.
  • Deploy earlier versions of permission schemes, including users, roles and memberships, etc.


Because drift correction uses a simple “compare source to destination” method to generate the sync script, there are a number of drawbacks:

  • It cannot revert complex changes, like table renames or columns splits.
  • By default, it will not allow actions that may result in data loss, such as reducing the precision/width of columns or removing of columns (ie. if the column was increased in size or added in the release).
  • By default, it will not allow NOT NULL constraints to be re-introduced, because this requires a full copy operation to be performed on the table.

The latter two restrictions can be lifted by specifying DriftOptionBlockDataLoss=False, however for obvious reasons, this is not recommended for Production environments.

Tools in this post


Develop and deploy databases in Visual Studio with migration scripts.

Find out more

Share this post.

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

Related posts

Also in Blog

You’re not delivering DevOps to the database

I’ve read through a number of the industry thought leaders to get an understanding of how DevOps is being communicated out there. As with so much else in life, you can start at Wikipedia to get a ge...

Also in Database DevOps & DLM

Where's the Ops in DevOps? Part 2

In this three-part series, guest bloggers from DevOpsGuys look at the real role of Ops in DevOps. Where it changes, how it changes, and why Ops has an important part to play in the brave new world of ...

Also about ReadyRoll

Moving from application automation to true DevOps by including the database

The recent State of Database DevOps Report revealed that within two years, 80% of companies will adopt DevOps. That’s an interesting finding in itself, but the report also showed that 75% of compani...