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

There's a somewhat big catch. The above process relies on being able to match objects in the dacpac to objects in the target database. If you rename a table, how would it know that [RenamedTableName] is the same table as [TableName]? From SqlPackage's perspective, it would think that [TableName] got dropped and [RenamedTableName] was created. (It won't actually drop it unless you tell it to, but that's a blog for another time)

The Solution

The solution is to be more explicit with when certain refactors happen. The dacpac carries instructions on refactors that happened that SqlPackage would not be able to intuitively know.

There's two mechanisms that enable this:

1. The <projectName>.refactorlog file that gets checked in that lets you record refactors
2. The __RefactorLog table that keeps track of which refactors have already been ran on a database

.RefactorLog File

The .refactorlog file consists of XML, mainly just a list of <Operation> elements. Each of those represent an individual refactor.

<Operation Name="Rename Refactor" Key="3a7b06a1-5e83-47f9-95d7-2a2fbaf2a20e" ChangeDateTime="06/10/2021 15:16:10">
  <Property Name="ElementName" Value="[dbo].[Country]" />
  <Property Name="ElementType" Value="SqlTable" />
  <Property Name="ParentElementName" Value="[dbo]" />
  <Property Name="ParentElementType" Value="SqlSchema" />
  <Property Name="NewName" Value="[CountryInfo]" />
</Operation>

The above is an example of a refactor that changes a table Country's name to CountryInfo. I wouldn't worry too much about learning the syntax for these entries, if you are using SSDT in Visual Studio it creates them for you.

__RefactorLog Table

This is a VERY simple table that just keeps track of which refactors have already been ran. Note that it automatically gets created when you deploy a dacpac to your database.

It only has one column, OperationKey, which just holds a list of GUIDs. Those are the GUIDs you can see in the Key field in the Operation entry shown above.

When you deploy a dacpac, it will fill this table with every refactor that got ran, to avoid it trying to run the same refactor every single time (which would obviously fail in most cases).

Example

Here's a brief example of changing a column name. I've started with a very simple SSDT project with just one table in it:

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

Now to test it WITHOUT the refactor log, let's just change the column name Name.

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

Generating a script for what this would do (doable from the publish window in Visual Studio), we get:

/*
The column [dbo].[Person].[Name] 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 [Name];
GO
ALTER TABLE [dbo].[Person]
    ADD [FullName] NVARCHAR (64) NULL;
GO

Obviously this is not what we were going for.

Undoing the manual change, and utilizing the Visual Studio Refactor option (I HIGHLY suggest using this as opposed to manually making refactor logs. It also will automatically update stored procedures, functions, views, etc. that were referencing the renamed table/column for you):

It has now created a ExampleDatabase.refactorlog (because this was the first refactor), with a single Operation entry:

<Operation Name="Rename Refactor" Key="0517c5b8-8ae1-4642-ba65-9465fa2daf3c" ChangeDateTime="06/10/2021 15:36:05">
  <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>

Generating the script again, we get:

GO
PRINT N'The following operation was generated from a refactoring log file 0517c5b8-8ae1-4642-ba65-9465fa2daf3c';
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 = '0517c5b8-8ae1-4642-ba65-9465fa2daf3c')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('0517c5b8-8ae1-4642-ba65-9465fa2daf3c')
GO

Which is the intended result.

Rolling Back Refactors

At some point I'll have an entire article on rolling back Dacpac deployments, or more accurately how you CAN'T roll back Dacpac deployments.

I think it's worth mentioning here, however, that there isn't really a mechanism for undoing refactor log changes. You can't simply deploy an older dacpac and have it undo refactors - it wouldn't even know what to undo.

The best (safest, most structured, controllable) mechanism to undo a refactor log is to create a new refactor that undoes it, and deploy that via a newer dacpac. SSDT is about always having builds move forward, even if "moving forward" is changes that undo the previous "move forward".

As someone who's manually deleted from the __RefactorLog table and undone schema changes by hand in the midst of urgent, super hot, situations, I can definitely say that the potential complications manual management of the __RefactorLog can introduce will almost never be a worthwhile risk versus moving forward with an "undo" refactor log committed.