3

I have an SSDT project with VS2015 and using SqlPackage.exe to generate deployment scripts and incremental update scripts. The database project contains post deployment scripts. here's how I generate the main script for DB installation:

sqlpackage.exe 
/a:Script 
/op:"database.sql" 
/sf:"database.dacpac"
/tsn:"localhost" 
/tdn:"MyDbName" 
/p:CommentOutSetVarDeclarations="True"
/p:ScriptDatabaseCompatibility="True" 
/p:IgnoreFileAndLogFilePath="True" 
/p:IgnoreFilegroupPlacement="True" 
/p:ScriptFileSize="True" 
/p:PopulateFilesOnFilegroups="False"

This will output database.sql file which I run with sqlcmd.exe. the script file also contains all my post deployment scripts (Insert, Update, etc..)

Once I have a new version of the database, I create incremental update script from previously taken dacpac snapshot:

sqlpackage.exe 
/a:Script 
/op:"%migratePath%\%database%.!build!-%version%.sql" 
/sf:"%snapshotPath%\%database%.%version%.dacpac" 
/tf:"%snapshotPath%\%database%.!build!.dacpac" 
/tdn:"MyDbName" 
/p:CommentOutSetVarDeclarations="True" 
/p:AllowIncompatiblePlatform="True" 
/p:IgnorePermissions="True" 
/p:IgnoreRoleMembership="True" 
/p:ExcludeObjectTypes=Users;Credentials;Logins;Permissions;RoleMembership;ServerRoles;ServerRoleMembership;

this works fine, however, incremental update script does not contain metadata/reference data (e.g. e.g. list of countries, states, currencies, etc.).

Is there a way to tell SqlPackage to generate incremental script that contains the table reference data as well?

Zohrab Broyan
  • 93
  • 9
  • 1
    Hi, Does the snapshot dacpac contain the post deploy script? You can open it with 7zip or name it .zip and see if there is a post deploy.sql. Can you elaborate on why you are comparing your desired schema with an old one, rollback? – Ed Elliott Jan 27 '16 at 06:39
  • Yes, the snapshots contain the postdeploy script – Zohrab Broyan Jan 27 '16 at 18:06
  • I compare them to generate the incremental script. maybe I'm doing it wrong and open to suggestions. I guess I need to better understand how to upgrade older versions of databases to the latest version. should I directly deploy dacapac with SqlPackage or should I execute incremental scripts with SqlCmd.exe – Zohrab Broyan Jan 27 '16 at 18:15
  • How this reference data file is defined? I would suggest to save it as a stored procedure with all data statements defined as MERGE. Then call this procedure in post-deployment script. – scar80 Jan 28 '16 at 07:18
  • So you're comparing a dacpac to another dacpac? I don't think you can get the post-deploy scripts when you do that. The concept doesn't mean the same thing when you're comparing dacpacs. – Peter Schott Jan 28 '16 at 16:21

0 Answers0