New Advanced Publish Options to Specify Object Types to Exclude or Not Drop

Our team has been hard at work these past few months, and we're thrilled to announce that the most frequently requested improvement to SQL Server Data Tools is here. We plan to release an update of SQL Server Data Tools in the coming weeks, and in that update you'll find new options that provide greater flexibility and control over database publishing.

We've re-organized the Advanced Publish Settings dialog and added new options on the Drop and Ignore tabs. On the Drop tab, selecting Drop objects in target but not in source now enables additional options for selecting object types, like Users, that will not be dropped. The need for this frequently arises with TSQL user objects, as they are often jointly managed by the developer and the database administrator. Until now, selecting Drop objects in target but not in source always caused any target-only user objects to be dropped during publish, but the new Do not drop users option leaves the choice up to you.

Do not drop users can be combined with do not drop permissions and do not drop role membership to ensure that settings for user objects that exist only in the target database are not modified.

Note that the new "do not drop" options are just concerned with target-only objects. Selecting Do not drop users, for example, will not prevent users that are defined in your database project from being created or altered – it only prevents users that aren't defined from being dropped. Sometimes, though, you just want a certain object type to be completely ignored when publishing a database, so in addition to the "do not drop" options, we've added options for excluding object types from publish.

On the Ignore tab, the new options in the Excluded Object Types section allow you to choose to prevent certain object types from being published. Excluded object types aren't evaluated during publish and new, modified, or deleted objects of an excluded type aren't created, changed or dropped in the target database. (Note, though, that it is sometimes necessary to modify an excluded object when one of its dependencies has been changed or removed.)

We've also modified SqlPackage.exe and the Data-Tier Application Framework (DacFX) to accept these new options.

We'd like to thank the user community for the clear and consistent feedback you have provided about the importance of these options, and we encourage you to tell us what we should be doing next by:

  • Submitting feedback and voting at Connect
  • Posting on our Forum
  • Submitting a comment below