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?

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.

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.