Dropping Objects with SSDT
Published: 2021-06-15 13:55:57

[SSDT](https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-ver15) 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](https://docs.microsoft.com/en-us/sql/relational-databases/data-tier-applications/data-tier-applications?view=sql-server-ver15) which is essentially a build. [SqlPackage](https://docs.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage?view=sql-server-ver15) 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.