In Visual Studio 2012, we have Schema Compare
in SSDT's SQL Server Database Project
(DbProject) project which helps
source
versus target
target
to make it the same as source
Where
My question is that is it possible to have and where can I get the command-line/API interface to call this feature?
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.