Unable to drop tables, functions and procedures

[kevcrooks]

Using version 2.6.1, I have started with a blank project, targeting a local db.

Firstly I added tables, functions and stored procedures to the project, and published. These appeared in my local database.

However, if I then delete these, and build/publish again, they are not removed from the database.

1. Project file, slightly edited after dotnet new sqlproj

   <Project Sdk="MSBuild.Sdk.SqlProj/2.6.1">
   <PropertyGroup>
       <TargetFramework>netstandard2.0</TargetFramework>
       <SqlServerVersion>Sql150</SqlServerVersion>
       <TargetServerName>(localdb)\MSSQLLocalDB</TargetServerName>
       <TargetDatabaseName>SqlProjTestDb</TargetDatabaseName>
   </PropertyGroup>
   </Project>

2. dotnet new table -n TableToDelete

3. dotnet new sproc -n SProcToDelete

4. dotnet new tablefunc -n TFuncToDelete

5. dotnet build and dotnet publish. Confirm that the local database has these items.

6. Delete the items created form steps 2.-4. and dotnet build and dotnet publish. First it seems that the dacpac isn't correctly updated (If I do a Schema Compare there are no changes). In order to update the dacpac I needed to first do dotnet clean followed by build/publish

7. After step 6, the Schema Compare shows that it does not match the local DB, as the local DB has not had the table, sproc or tablefunc removed.

Am I missing a step here, to get the build/publish step to remove any deleted items from the target database?

Update: After looking here: https://learn.microsoft.com/en-us/answers/questions/811899/sqlpackage-does-not-delete-the-tables-from-target it was suggested to set the property DropObjectsNotInSource, which I can do like this: <DropObjectsNotInSource>true</DropObjectsNotInSource> in the project file. Is this the correct way to solve the issue?

[jmezach]

@kevcrooks Yes, I believe this is by design to avoid you accidentally deleting objects from the target database. If you really want to drop objects in the target database you have to be explicit about it by indeed setting DropObjectsNotInSource to true. You can also do this from the commandline, both with SqlPackage and with dotnet publish. For the latter you could add /p:DropObjectsNotInSource=true to the command and it should work. For SqlPackage I don't know out of the top of my head, but it is probably something similar.

Of course you can also but it in the project file directly, but keep in mind that this will always drop objects from the target database when running dotnet publish which might not be what you want depending on your scenario.

[ErikEJ]

You need to run a script prior to .dacpac execution - for a sample of how to do this, see https://segunakinyemi.com/blog/dacpac-dropping-objects/

[jeffrosenberg]

@ErikEJ - I think that very much depends on your deployment strategy. FWIW, I've used dacpacs for years without needing to resort to something like a "pre-pre-deployment script."

@kevcrooks - YMMV, but one other parameter that you might find helpful (although the author of @ErikEJ's article didn't!) is /p:BlockOnPossibleDataLoss. This makes it safer to permanently turn on DropObjectsNotInSource, because it will halt deployment if it detects that data might be deleted. So dropping empty tables or columns works, but dropping tables or columns with data will error.

The way I've typically handled this is to set DropObjectsNotInSource=true and BlockOnPossibleDataLoss in my project file. Then, if I need to drop a table with data in it, I can pass /p:BlockOnPossibleDataLoss=false at the command line. I will also typically set BlockOnPossibleDataLoss=false in local dev environments.