0

I'm trying to get SQLPackage for schema compare working from CMD line. Have to compare a master db to 300 live dbs. All 2014 SQL and none registered as data-tier applications. PowerShell and 3rd party tools are not an option at this time.

I've followed the scripts in the first answer here: https://stackoverflow.com/questions/20673516/command-line-api-for-schema-compare-in-ssdt-sql-server-database-project

I can successfully create .DACPAC files from my source and my targets.

If I add some objects (1 table, 3 views) to "Test_B" database, the compare script does not show those differences when I compare the master to either the database itself or to the .dacpac that was generated. BUT, if I run a SSDT schema compare from VS, it does pick them up and will create a .sql script with the expected DROP statements.

So the question: What am I doing wrong at the cmd line that Visual Studio/SSDT is doing right? Missing a parameter? Wrong order of things?

#Compare DACPACs

sqlpackage.exe /a:Script /sf:C:\Users\hillke4\Documents\SQLScripts\DACPACS\ACACalculationDB.JTA.v7.master.dacpac /tf:C:\Users\hillke4\Documents\SQLScripts\DACPACS\ACACalculationDB.JTA.v7.Test_B.dacpac /tdn:ACACalculationDB.JTA.v7.Test_B /op:C:\Users\hillke4\Documents\SQLScripts\DACPACS\Deltas\ACACalculationDB.JTA.v7.Test_B.sql


#Compare master dacpac to database

sqlpackage.exe /a:Script /sf:C:\Users\hillke4\Documents\SQLScripts\DACPACS\ACACalculationDB.JTA.v7.master.dacpac /tsn:US1213113W1\SQL2014 /tdn:ACACalculationDB.JTA.v7.Test_B /op:C:\Users\hillke4\Documents\SQLScripts\DACPACS\Deltas\ACACalculationDB.JTA.v7.Test_B.sql
1

The issue is that the default deployment options when using Script or Deploy actions are set up to avoid dropping objects so as to minimize the risk of data loss. Schema Compare, on the other hand, is targeted at an interactive mode where the actions (Add, Delete, Update) are much clearer so this doesn't occur. To generate the Drop syntax as expected, add the property shown below (from the SqlPackage.exe documentation) to your call.

Property: /p:DropObjectsNotInSource= {True | False}

Default: False

Description: Specifies whether objectsthat do not exist in the database snapshot (.dacpac) file will be dropped from the target database when you publish to a database.

  • perfect! tested and verified :) I remember seeing this 2 months ago when I first started looking at automating...and then completely forgot about it. I'm pretty sure you just saved me a bunch of hours of manually clicking through all the databases! My – Kevin3NF Aug 17 '16 at 15:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.