SSDT is a tool for source controlling SQL Schema.
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.
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.
SqlPackage has two major mechanisms to enable avoiding this:
- 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.
- 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.
The SqlPackage parameter is
DropObjectsNotInSource, in visual studio you can find this under
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.
The SqlPackage parameter is
BlockOnPossibleDataLoss, in visual studio you can find this under
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.
I have a quick example SSDT project, it has a single
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
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
USE [$(DatabaseName)]; GO PRINT N'Dropping [dbo].[Person]...'; GO DROP TABLE [dbo].[Person]; GO PRINT N'Update complete.'; GO
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 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
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.