THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

Detecting Database Drift using SSDT

One of the nice things about doing user group presentations is that when you’re putting the presentation together you invariably learn about features that were previously unbeknown to you; so it proved as I stumbled upon SSDT’s database drift detection features while researching material for my forthcoming pre-conference seminar SSDT from the ground up.

What is database drift?

You have probably experienced database drift, you just didn’t happen to refer to it as that. More likely you might have spluttered the following, perhaps sprinkled with a few expletives:

  • “Who put these tables in my database?”
  • “Who changed this view definition?”
  • “Why is this guy in db_owner?”
  • “Where has my stored procedure gone?”

In other words database drift can loosely be described as

stuff that appears, gets removed, or gets modified in your production databases that perhaps shouldn’t be

Detecting database drift using SSDT

If you’re using SSDT to manage your database schema then you probably consider the source code in your SSDT projects to be “the truth” and hence anything that appears in your databases that is not in your source code would be considered database drift.

In order to detect database drift using SSDT you must ensure that your database is registered as a Data-Tier Application. This can be done when you publish your database project (i.e. dacpac) by selecting “Register as a Data-tier Application”:


Thereafter you can check for database drift on subsequent publishes by selecting “Block publish when database has drifted from registered version”:


If you check that box and database drift has occurred then the publish operation will fail and you see an appropriate message in the Data Tools Operations pane, “Publish stopped. The target database has drifted from the registered version.”:


Clicking the View Report hyperlink displays the Drift Report which is represented in an XML file:

<?xml version="1.0" encoding="utf-8"?>
<DriftReport xmlns="">
    <Object Name="[View_1]" Parent="[dbo]" Type="SqlView" />
    <ExtendedProperty HostName="[View_1]" HostParent="[dbo]" HostType="SqlView" Count="2" />
  <Removals />
  <Modifications />

In this case a view called [dbo].[View_1] has been added to the target database. That view did not exist in the dacpac that was most recently deployed against the database thus the publish operation fails. Keeping one’s deployed databases as “clean” as possible is something that I am all in favour of so personally I think this is a pretty cool feature.

Generating a drift report from the command-line

The drift report can be generated by the command-line tool sqlpackage.exe. To do so you need to define:

  • the action to be DriftReport
  • a target server & database
  • an output file

>SqlPackage.exe /A:DriftReport /tsn:"(localdb)\Projects" /tdn:"Database1" /op:DriftReport.xml


As far as I know there is no support for generating a drift report from SQL Server Management Studio (SSMS). I’m hoping that changes so that this feature gets more visibility.


If you have any comments stick them in the comments section below!


Published Thursday, April 4, 2013 11:31 AM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Kent Chenery said:

Hi Jami,

SSMS does have drift detection, but its not that obvious where it is.  Once you've registered a database as a data-tier application you can "upgrade" it through SSMS (Right click on the DB --> Tasks --> Upgrade data-tier application).  Select your DACPAC file, click next and the wizard does the drift detection (listed as Detect Change).

You can then save the report and choose to proceed if you're happy to.

In saying that, I've never used it in anger.  We tend to work like this: Just before a release, do the drift detection manually.  Look at the report and decide what to "wash back" into the project.  Thats then pushed through our CI to bring dev, test, stage, etc "into line".  When we're happy its all pushed into prod.  Its not perfect and we're refining all the time but its working for us at the moment.

April 4, 2013 6:02 AM

jamiet said:

Thanks for that Kent.

How do you "do the drift detection manually"? Do you use Schema Compare?


April 4, 2013 11:23 AM

Jack Corbett said:


Do you need to be using SSMS 2012 to do this?  I don't see the Upgrade Data-Tier application option in the Tasks menu in SSMS 2008R2.

June 12, 2013 12:03 PM

Matt Potter said:

I know this is off at a slight tangent, but fairly similar - I'm trying to automate doing a schema compare as part of our build process without much success and was wondering if there's any way you know of to do a drift report between 2 databases or even better a schema compare.

All I can find are examples of this in VSDBCMD, but since we've upgraded to SSDT I can't find any way of doing this. It seems like this has been released without porting over all the previous functionality and i'm trying to find a workaround without having to code it in c#.

Any advice would be much appreciated.



November 4, 2013 1:59 PM

Peter Schott said:

@Matt, isn't that what Jamie mentioned with his SQLPackage syntax to generate the drift report? Add that in with a command to build the package, then compare the project to the package or the other way around. You should also be able to specify a source DB/connection in addition to the target DB. I've done this and normally use the DB as target compared to the package as source. It will generate an XML file. For our releases, we'd automate this with a command line task to run SQL Package.

November 4, 2013 2:09 PM

Mike said:

I realize this is an old post, but I've been trying to find some information on this (Register as Data-tier Application).

Since the Advanced button is disabled once this option is selected, what is the scope on publishing?

In other words, without this checked we have a slew of options that we can define how publishing works, but now that it's grayed out I'm not sure how my publish will react to certain situations.

August 12, 2014 5:10 PM

Debarati said:


Does the SSDT schema compare works if one of the source is not SQL SERVER (Say DB2). I am unable to change DataSource as the "Change" button is Disabled.

Any suggesstion will be a great help.



September 18, 2014 5:06 AM

jamiet said:

Hi Debarati,

At the time of writing Schema Compare only works against SQL Server databses.



September 18, 2014 5:11 AM

Leave a Comment


This Blog


Privacy Statement