Command-line/API for Schema Compare in SSDT SQL Server Database Project?

Question

In Visual Studio 2012, we have Schema Compare in SSDT's SQL Server Database Project (DbProject) project which helps

1. Compare source versus target
2. Update target to make it the same as source

Where

1. Source and target can be either a database, a DbProject project, or a .dacpac file
2. Update can be done via an update action or generated script

My question is that is it possible to have and where can I get the command-line/API interface to call this feature?

Daniels

Answer 1

SOURCE Database

sqlpackage.exe /a:Extract /scs:Server=%Server%;Database=AspBaselineDB; /tf:%DriveSpec%\%DacPath%\%AspBaselineDB%_baseline.dacpac

TARGET Database

sqlpackage.exe /a:Extract /scs:Server=%Server%;Database=%AspTargetDB-2%; /tf:%DriveSpec%\%DacPath%\%AspTargetDB%.dacpac

COMPARE & GENERATE the Delta script

sqlpackage.exe /a:Script /sf:%DriveSpec%\%DacPath%\%AspBaselineDB%_baseline.dacpac /tf:%DriveSpec%\%DacPath%\AspNetDb\%AspTargetDB%.dacpac /tdn:aspTargetdb /op:%DriveSpec%\%SqlPath%\AspNetDb\AspDbUpdate.sql

EXECUTE the script

sqlcmd.exe -S %Server%\aspnetdbAmexDev -i %DriveSpec%\%SqlPath%\AspNetDb\AspDbUpdate.sql

I do this in CMD scripting as our IT dept will not allow unsigned PowerShell scripts and they won't purchase a cert. This works flawlessly, even when calling it from TFS 2012 Team Builds or simply executing the .CMD script from a VS command prompt as Administrator.

Note!

Add the following SET in your script: SET PATH=%PATH%;C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin

Also: as you can see I set path variables. I do this as I am touching up to 50 flavors of the database and this is the only consistent way I have found to generate delta scripts and update our DEV and TEST databases.

Painter

Can we also compare specific user table(s) data and generate the script? – Dierolf

is there any way to do this for a certain table, or item? Schema Compare in Visual Studio and Update will allow people to manually pick and choose, how do I do this in command line? – Ruhr

Answer 2

At present, the only way to get API access to schema compare results is by writing a deployment plan modifier/executor that runs during a deploy/script operation in the DacServices API. This lets you examine the deployment plan generated when comparing a dacpac against a database, but it also gives access to the ModelComparisonResult that represents a schema compare operation, which is available in the context object passed to the OnExecute method of a contributor. I've just written a blog post that covers this process and might be useful to you - take a look at that and hopefully it'll help you get started.

Metathesis

Need a nice powershell one liner to do this :) – Bruckner

Answer 3

I wrote a few lines on this topic a while ago. Hopefully you find them useful.

In a nutshell, the tool you are looking for is vsdbcmd.exe, which is distributed by Ultimate and Premium versions of Visual Studio.

Ilex

Cool! This seems to be the one I need. – Daniels

I'm using Visual Studio 2012. VSDbCmd.exe seems to be for Visual Studio 2010 right? – Daniels

It is unlikely to be available for Visual Studio 2012 as mentioned here social.msdn.microsoft.com/Forums/en-US/… – Daniels

SqlPackage is an updated version of vsdbcmd.exe. It has equivalent functionality. @NamG.VU - you said in response to a previous answer that SqlPackage wouldn't help, but here you say that vsdbcmd would? What exact feature do you need that SqlPackage doesn't have? – Metathesis

@Nam I wasn't aware they had removed vsdbcmd from vs2012. Hopefully you can perform the same operation and mechanics using SqlPackage which at first glance seems to be pretty similar (msdn.microsoft.com/en-us/library/hh550080(v=vs.103).aspx) – Ilex

@Kevin SqlPackage seems to be not the same as the GUI tool of Schema Comapre thab the VSDbCmd – Daniels