3

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?

Community
  • 1
  • 1
Nam G VU
  • 33,193
  • 69
  • 233
  • 372
  • 1
    #1 - I think there's an option you can pass to ignore users/permissions. I've got those set in my advanced publish profile to avoid affecting users. It seems to work. #2 - Haven't hit this yet. Have you tried generating a custom script to get you started or using pre-deploy scripts to avoid some of the issues around potential data loss? – Peter Schott Jan 03 '14 at 19:20
  • @PeterSchott @1 What is that option you are using in your profile? – Nam G VU Jan 05 '14 at 20:00
  • 1
    False False True – Peter Schott Jan 06 '14 at 20:10
  • The arguments seem the same as my command-line's ones. I will try to use profile and back with you later – Nam G VU Jan 07 '14 at 03:41

4 Answers4

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.

Community
  • 1
  • 1
Matt
  • 1,494
  • 2
  • 18
  • 38
1

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

Farax
  • 1,447
  • 3
  • 20
  • 37
0

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

enter image description here

0

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.

Ε Г И І И О
  • 11,199
  • 1
  • 48
  • 63