12 May 2013
12 May 2013

Switching branches using drift correction

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 Hub

Tackle common database problems more effectively

While the database is sometimes seen as the blocker in the development process, it doesn’t have to be. With the right knowledge and tools, you can stop problems before they occur, help to speed up d...

Also in Product learning

Get a lot more value from your tools

From provisioning databases through to version control, automated deployments and monitoring, Redgate has a tool for every part of the database development process.

You probably have a favored tool b...

Also in ReadyRoll

Solving Visual Studio database delivery problems with ReadyRoll

For .NET developers working with Visual Studio (VS), the introduction of Database Projects with SQL Server Data Tools (SSDT) brought to VS the ability to manage changes to the database schema and cod...

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...