Dropping Objects with SSDT

SSDT is a tool for source controlling SQL Schema.

How SSDT works

It works ALMOST exactly like you would expect it to work: you add CREATE scripts to define objects. You publish this script utilizing a Dacpac which is essentially a build. SqlPackage uses this dacpac to diff what's in your build versus what's in the target database, and creates scripts to resolve differences.

The Catch

Accounting for human error means accepting the possibility that a typo can lead to an entire table to be dropped. Simply renaming a column or table and not checking in a refactor log, without checks could lose irrecoverable data.

The Solution

SqlPackage has two major mechanisms to enable avoiding this:

1. Dacpac deploys will NOT by default drop objects it finds in the target database that do not exist in the source. This means just deleting a table in SSDT will not try to drop it on a deploy. This does not apply to columns.
2. Before a dacpac deploy tries to delete any object that can hold data (I.E: a column, table) it does a select to check if there's data that would be deleted. If it finds data, it aborts the operation.

There are options you can enable in SSDT to disable these layers of protection. It is HIGHLY recommended that you do not enable these options except when you have time to pay extra attention to a deployment.

Drop Objects In Target But Not In Source

The SqlPackage parameter is DropObjectsNotInSource, in visual studio you can find this under Advanced -> Drop -> Drop objects in target but not in source.

This does NOT disable the second layer of protection (preventing data loss), but it will cause the deploy to ATTEMPT to drop tables/stored procedures/etc. that do not exist in the dacpac.

It is important to note that you can specify what types of objects to NOT drop when using this. For instance, if you don't use dacpac deploys to manage Users/Permissions/etc. you can set DoNotDropObjectTypes=Users,Permissions to NOT drop those.

Block Incremental Deployment If Data Loss Might Occur

The SqlPackage parameter is BlockOnPossibleDataLoss, in visual studio you can find this under Advanced -> Block incremental deployment if data loss might occur.

This is the big scary one you should be extremely careful with. Disabling it causes any drops that would occur to happen without checking for data first.

Example

I have a quick example SSDT project, it has a single Person table:

CREATE TABLE [dbo].[Person]
(
  [Id] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
  [Name] NVARCHAR(64)
)

If I delete Person.sql and generate a publish script:

USE [$(DatabaseName)];
GO
PRINT N'Update complete.';
GO

Nothing happens.

Now, if we enable DropObjectsNotInSource, and generate the script again:

USE [$(DatabaseName)];
GO
/*
Table [dbo].[Person] is being dropped.  Deployment will halt if the table contains data.
*/
IF EXISTS (select top 1 1 from [dbo].[Person])
    RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT
GO
PRINT N'Dropping [dbo].[Person]...';
GO
DROP TABLE [dbo].[Person];
GO
PRINT N'Update complete.';
GO

And once more, with both DropObjectsNotInSource enabled AND BlockOnPossibleDataLoss DISABLED:

USE [$(DatabaseName)];
GO
PRINT N'Dropping [dbo].[Person]...';
GO
DROP TABLE [dbo].[Person];
GO
PRINT N'Update complete.';
GO

Recommended Processes For Deleting Objects

I highly recommend avoiding disabling BlockOnPossibleDataLoss as much as possible. From experience, it is VERY easy for a small oversight or misunderstanding of a dacpac/build to lead to losing some critical data.

The Simple Process

The simplest course of action for dropping tables is to manually delete all rows in the table when you're 100% confident they are not used, then they'll be dropped with no complaints with a normal DropObjectsNotInSource.

A More Controllable Process

A process that takes a bit more overhead but provides more confidence before dropping objects is to deprecate tables/columns/etc. before dropping them.

You can utilize a deprecated schema (you can use schema specific permissions to enforce applications not accidentally using things in this schema) that you move tables/etc. into to mark them for deletion. Or append _deprecated to column names to mark them as dead. This will also give you time to roll back the deprecation if you do find something still using it.

Then, on some regular basis, you can go through and delete all deprecated objects at once. When you do this you will want to pay extra attention to the generated script BEFORE you deploy it. Make sure it's only going to drop what you expect it to drop.

You can do this in visual studio by selecting Generate Publish Script instead of Publish in the Publish window (I wish those weren't right next to each other). This is actually a good habit to get into every now and then to get insight into what deploys actually do.