DevOps: Deleting stored procedures and views in target database when using database projects

We’ve been using SQL Server database projects ever since they were called “Visual Studio Team Edition for Database Professionals”. That was back in 2005 and the name was quite a mouthful.

Until the last year or so though, we’ve only seen fairly patchy adoption of them among our customers. Over the last year or so, this has really changed. We’re seeing so many customers starting to use them. Why?

I’m guessing that it’s the increased interest in using DevOps.

One question that I hear again and again though relates to deployment. Developers assume that if you delete a stored procedure or view from the database project, that it will be deleted from the target database when you publish the project.

By default, that won’t happen but it’s easy to change.

When you are in the Publish Database window, click Advanced.

In the Advanced Publish Settings window, select the Drop tab:

The option that you’re looking for is Drop objects in target but not in source:

You can then choose which types of objects that this applies to.

2018-06-01