What is the right parameter for SqlPackage.exe to publish a .dacpac file that can drop the absent objects on target database?

Question

As we can use SqlPackage.exe from Microsoft SSDT in Visual Studio 2012 to sync database as discussed here, I'm using it to sync target database, called TargetDb, to match with the source database objects stored in an SQL Server Database project, called DbProject.

The action /a:Publish of SqlPackage.exe command allows us to sync the DbProject's .dacpac file to TargetDb but the default arguments will NOT drop TargetDb's objects which not exist in DbProject.

Turning on the flag /p:DropObjectsNotInSource=true will solve this but also creates weird behaviors

1. The flag /p:DropObjectsNotInSource=true will drop the user/login objects on TargetDb which is absolutely not expected! The closest ones I can find is /p:DropRoleMembersNotInSource=false /p:DropPermissionsNotInSource=false but those do not help much.
2. The flag /p:DropObjectsNotInSource=true will break the flag /p:BlockOnPossibleDataLoss=true which means if data-loss occurs, the update action(s) will not be blocked; that is NOT what I want at all.

Currently I have to accept 'trash'/redundant objects on TargetDb :(

What are better flags to use to get me there?

Answer 1

I asked a similar question here and ended up scripting the users as a post-deploy script to recreate them after deploying the database upgrade.

The DropPermissionsNotInSource property only applies to GRANT / DENY permissions. DropRoleMembersNotInSource is just for role membership.

Sadly there isn't an option to exclude users from the objects being dropped when the DropObjectsNotInSource property is set to true.

We also make certain changes (like changing column types) in pre-deploy scripts, as BlockOnPossibleDataLoss can prevent deployment even when the change won't cause data loss. I guess it's good it's over cautious rather than not cautious enough.

Answer 2

Please be aware that that "prevent data loss" could has also to be set in the project file:

Answer 3

Use ExcludeObjectTypes to ignore the users/logins.

/p:DropObjectsNotInSource=true /p:ExcludeObjectTypes=Users;Logins;RoleMembership;Permissions;Credentials;DatabaseScopedCredentials

/p:BlockOnPossibleDataLoss=true has no effect when /p:DropObjectsNotInSource=true, so don't bother ticking that off.

Answer 4

while the answer from @Ε Г И І И О accomplishes the objective, there is a more specific solution to the DROP scenario:

One issue when using /p:ExcludeObjectTypes is the object types get excluded from the whole deployment, including CREATE and ALTER statements, and these might be needed in some cases.

Microsoft details on the sqlpackage properties DropObjectsNotInSource and DoNotDropObjectTypes

the /p:DoNotDropObjectTypes property can only be set when used together with /p:DropObjectsNotInSource, this way we are only restricting/excluding the object types from the DROP statement, but we are not affecting CREATE and ALTER.

Both ways work, but if you need CREATE and ALTER then it is better to use /p:DoNotDropObjectTypes

Edit 2024-03-15: with our team we have reviewed this topic, and we think there is a better approach, but we would need Microsoft to include new properties for sqlpackage.exe new properties IncludeObjectTypes, DropObjectsTypesNotInSourceList. If you like the idea an upvote in the Microsoft URL would be greatly appreciated. Thanks!

Answer 5

You should try using the /p:DoNotDropObjectType parameter which would allow you to specify which type you want to drop that is not in the source i.e. when DropObjectsNotInSource is true