Rolling Back SSDT (And Why You Can't)

SSDT is a tool for source controlling SQL Schema.

How SSDT works

It works very similar to application builds. SSDT compiles "code" (CREATE scripts) into a "build" (a Dacpac), and you can deploy it (generally using SqlPackage) to a database.

You can even publish it from visual studio the same way you might an application. There's a ton of parallels in their functionality.

The Catch

In most applications, when you deploy a bad build and a fix is not available in a timely manner, you can typically just deploy the previous build to "roll it back".

This does not work with dacpac deploys. Dacpacs are designed to only deploy in a forward, progressive manner.

There's a number of reasons for this:

• SqlPackage and the database will not know how to revert refactors that have been ran (this is the big one)
• Columns that were added to populated tables cannot be deleted without disabling BlockOnPossibleDataLoss (very high risk)
• Newly created objects will not be dropped unless you enable DropObjectsNotInSource

There's some other smaller issues you can run into in edge cases as well.

The Solution

It's generally pretty simple, to fix a bad dacpac/build, you simply create a new one with whatever refactorlog changes or table changes are needed to make the fix.

For higher risk changes (changing column names for example) it can be suggested to have a "just in case" dacpac ready that would undo the one you just deployed, in the event you need to urgently "roll it back".

The philosophy, much like many CI/CD philosophies, is to keep builds moving forward, never taking steps back.

It might also be worth noting there are mechanisms like synonyms and computed columns for minimizing risk of changing names.

Example

I have a very simple SSDT example project with a single Person table:

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

I've created Dacpac 1 for this, and deployed it.

Let's roll out a change to the name column, using the Visual Studio refactor tool to refactor the column Name to FullName:

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

This also creates a refactor log entry:

<Operation Name="Rename Refactor" Key="813ae462-b83f-4dc8-ab48-12c56b09137b" ChangeDateTime="06/10/2021 17:37:09">
  <Property Name="ElementName" Value="[dbo].[Person].[Name]" />
  <Property Name="ElementType" Value="SqlSimpleColumn" />
  <Property Name="ParentElementName" Value="[dbo].[Person]" />
  <Property Name="ParentElementType" Value="SqlTable" />
  <Property Name="NewName" Value="[FullName]" />
</Operation>

I created Dacpac 2 for this, and deployed it. The script looks like:

PRINT N'The following operation was generated from a refactoring log file 813ae462-b83f-4dc8-ab48-12c56b09137b';
PRINT N'Rename [dbo].[Person].[Name] to FullName';
GO
EXECUTE sp_rename @objname = N'[dbo].[Person].[Name]', @newname = N'FullName', @objtype = N'COLUMN';
GO
-- Refactoring step to update target server with deployed transaction logs
IF OBJECT_ID(N'dbo.__RefactorLog') IS NULL
BEGIN
    CREATE TABLE [dbo].[__RefactorLog] (OperationKey UNIQUEIDENTIFIER NOT NULL PRIMARY KEY)
    EXEC sp_addextendedproperty N'microsoft_database_tools_support', N'refactoring log', N'schema', N'dbo', N'table', N'__RefactorLog'
END
GO
IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = '813ae462-b83f-4dc8-ab48-12c56b09137b')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('813ae462-b83f-4dc8-ab48-12c56b09137b')
GO

But oh no! Everything broke cuz we forgot to update the application first.

Trying to redeploy Dacpac 1, the script ends up looking like:

/*
The column [dbo].[Person].[FullName] is being dropped, data loss could occur.
*/
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'Altering [dbo].[Person]...';
GO
ALTER TABLE [dbo].[Person] DROP COLUMN [FullName];
GO
ALTER TABLE [dbo].[Person]
    ADD [Name] NVARCHAR (64) NULL;
GO

That doesn't actually undo the rename. It drops FullName and creates Name. Or, more accurately, it fails because there is data in the table.

So the resolution is to create a new build. I use the Visual Studio refactor tool to rename the column FullName to Name:

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

Now the refactor log has 2 entries:

<Operation Name="Rename Refactor" Key="813ae462-b83f-4dc8-ab48-12c56b09137b" ChangeDateTime="06/10/2021 17:37:09">
  <Property Name="ElementName" Value="[dbo].[Person].[Name]" />
  <Property Name="ElementType" Value="SqlSimpleColumn" />
  <Property Name="ParentElementName" Value="[dbo].[Person]" />
  <Property Name="ParentElementType" Value="SqlTable" />
  <Property Name="NewName" Value="[FullName]" />
</Operation>
<Operation Name="Rename Refactor" Key="7c29f405-8585-41ed-8ae0-47ffb0ec8128" ChangeDateTime="06/10/2021 17:43:44">
  <Property Name="ElementName" Value="[dbo].[Person].[FullName]" />
  <Property Name="ElementType" Value="SqlSimpleColumn" />
  <Property Name="ParentElementName" Value="[dbo].[Person]" />
  <Property Name="ParentElementType" Value="SqlTable" />
  <Property Name="NewName" Value="[Name]" />
</Operation>

Generating this as the build Dacpac 3, the script comes out looking like:

PRINT N'The following operation was generated from a refactoring log file 7c29f405-8585-41ed-8ae0-47ffb0ec8128';
PRINT N'Rename [dbo].[Person].[FullName] to Name';
GO
EXECUTE sp_rename @objname = N'[dbo].[Person].[FullName]', @newname = N'Name', @objtype = N'COLUMN';
GO
-- Refactoring step to update target server with deployed transaction logs
IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = '7c29f405-8585-41ed-8ae0-47ffb0ec8128')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('7c29f405-8585-41ed-8ae0-47ffb0ec8128')
GO

Which successfully gets us back to a working application.

For science, lets look at what happens when this is deployed to a database that has not had either Dacpac 2 or Dacpac 3 deployed to it:

-- Refactoring step to update target server with deployed transaction logs
IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = '813ae462-b83f-4dc8-ab48-12c56b09137b')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('813ae462-b83f-4dc8-ab48-12c56b09137b')
IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = '7c29f405-8585-41ed-8ae0-47ffb0ec8128')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('7c29f405-8585-41ed-8ae0-47ffb0ec8128')
GO

It's smart enough to know it does not need to change the column Name to FullName and then back to Name. It just inserts the refactor logs, and does nothing else.

Manual Rollbacks

I'm going to call this out as a capability, but would really like to emphasize that it can introduce a lot of risk and is not suggested except for the most dire and urgent of situations.

If you were to manually undo a refactor, as in via non dacpac generated SQL execute a "ALTER TABLE..." script and delete the entry from "__RefactorLog", you can effectively put the database into a state as if the refactor had never happened.

This introduces a lot of complex, risky, variables. Especially if you are using database versioning, you could have a database that thinks it's on one version but is actually missing a piece of it. You could potentially have one environment that has undone one refactor but other environments haven't, so you get inconsistent behavior of subsequent deploys. If you botch the manual rollback, you're going to end up in a weird state where further dacpac deploys do weirder things that can potentially make your database state even worse.

So to summarize, except in very very rare edge cases (that we should design process to avoid), rollbacks for dacpacs don't really exist. Keep things progressing forward.