How to delete stored procedures from a database project and have that reflect on the server?

Question

I have a Visual Studio database project with several stored procedures. I deleted one of them and then published the project. But the SP was still present on the published database and I had to delete manually.

I would want the VS project to be the point of truth for the database. But at the same time, I'm publishing to a production database that has data.

Is there a way to delete files in a VS database project so that those changes reflect on the published database?

Answer 1

The easiest way is using "Schema compare", where you would manually select objects to be dropped/ignored/added. to do this:

1. Right click the database in the solution explorer (choose schema compare)
2. Connect to your database
3. Click the 'compare' button
4. select/deselect differences changes that will be made
5. click the 'update' button
6. *note: if you want to NOT block on possible data loss, you need to configure that BEFORE comparing schemas. Do this by clicking the little settings icon

If you wish to DELETE (DROP) database objects in production database, that do not exist in your Database Project, you should successfully Build and Publish the project on production database using default settings.

If you have performed the Build successfully, proceed like this:

Check to see if this checkbox is deselected:

1. In Solution Explorer, right-click your database and select "Publish".
   "Publish Database" window should open

2. Click the "Advanced..." button.
   "Advanced Publish Settings" window should open

3. Switch to "Drop" tab

4. Deselect the checkbox "Do not drop stored procedures".
   Click OK and continue with publish.

Answer 2

A quick search led me to the following information. You may have already done everything that I'm referencing. If that is the case, simply ignore my answer.

According to How to: Delete Database Objects

For the change to be reflected on the database server, you must build and deploy your changes. For more information, see How to: Build a Database Project to Generate a Compiled Schema (.dbschema) File.

After you change the database project, you must prepare the build script to either create or update the target. You specify a new or existing database as the target by configuring build settings in the project properties. After you configure those settings, you can build the database project, which assembles any pre-deployment and post-deployment scripts together with the object definitions from the database project. After you prepare the database build script, you can deploy it to the target's database server.