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

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.


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.

Leave a Comment