Every Deployment Script Needs a Tested Undo Script

A deployment should arrive with a tested route back from its expected failure cases. Writing the undo script alongside the change forces you to decide what evidence and safeguards reversal will need.

Define What the Undo Script Must Return

An undo can restore a previous definition, reverse selected data values, or recover an earlier database state. Those outcomes are different. Decide which state must be restored, which new application activity must remain, and how long the undo remains valid. A vague promise to roll back is insufficient after the original transaction has committed.

I write the reversal while the intended change is still small and understandable. That exposes missing old values and dependencies before release night. Record the accepted baseline and the identity of the rehearsal copy. A saved script without a verified starting state cannot prove that its inverse will restore the right definition.

Use fresh scratch objects for the following examples. They illustrate distinct undo patterns, rather than one script to run against an application database unchanged. Rehearse both directions on a restored copy with representative data and permissions. A database's quiet empty development version can conceal the exact condition that makes production reversal fail.

Add a Column With a Guarded Removal

A new nullable column starts with no values. Its undo can remove the column only while that assumption remains true and no accepted dependency requires it. This lab table also has a rowversion column for the later data example. Create these objects only in an approved scratch database.

CREATE TABLE dbo.DeploymentItem
(
    ItemID int NOT NULL PRIMARY KEY,
    Amount decimal(12,2) NOT NULL,
    RowStamp rowversion NOT NULL
);
INSERT dbo.DeploymentItem (ItemID,Amount) VALUES (1,10.00),(2,20.00);
ALTER TABLE dbo.DeploymentItem ADD DeploymentTag nvarchar(30) NULL;

The removal checks current data rather than assuming nobody has used the new field. Dynamic execution ensures the column reference is compiled only when the column exists. A dependency still causes the DROP to fail; review and resolve it deliberately rather than expanding the undo to remove unrelated objects automatically.

IF COL_LENGTH(N'dbo.DeploymentItem',N'DeploymentTag') IS NOT NULL
    EXEC sys.sp_executesql N'
IF EXISTS (SELECT 1 FROM dbo.DeploymentItem WHERE DeploymentTag IS NOT NULL)
    THROW 50000,''The new column contains values; removal is not approved.'',1;
ALTER TABLE dbo.DeploymentItem DROP COLUMN DeploymentTag;';

Control concurrent writers during this schema reversal. A separate check is not a promise that another session cannot add a value before the DROP acquires its lock. Use a reviewed application pause or a tested locking strategy. Removing a populated field requires a data-preservation decision, not a relaxed guard.

Preserve the Procedure Before Replacing It

Save the exact module text and its relevant creation settings before changing a procedure. The lab creates its procedure with CREATE OR ALTER, but the stored definition still begins with plain CREATE. Running that saved text against the existing procedure fails with Msg 2714, so the restore step below rewrites the first keyword before executing it.

CREATE OR ALTER PROCEDURE dbo.ReadDeploymentItem
AS
BEGIN
    SET NOCOUNT ON;
    SELECT ItemID,Amount FROM dbo.DeploymentItem;
END;
GO
SELECT OBJECT_DEFINITION(OBJECT_ID(N'dbo.ReadDeploymentItem')) AS DefinitionText,
       uses_ansi_nulls,uses_quoted_identifier
INTO #ModuleBefore
FROM sys.sql_modules
WHERE object_id = OBJECT_ID(N'dbo.ReadDeploymentItem');
GO
CREATE OR ALTER PROCEDURE dbo.ReadDeploymentItem
AS
BEGIN
    SET NOCOUNT ON;
    SELECT ItemID,Amount FROM dbo.DeploymentItem ORDER BY ItemID;
END;
GO

Keep the temporary capture in the same session for this rehearsal. A deployment package needs durable storage outside a disposable session and a verified non-NULL definition. Encrypted text or insufficient metadata permission cannot supply a usable undo. Preserve permissions, signatures, and relevant execution context as separate evidence where required.

Restore the Module Definition With the Undo Script

Execute the saved lab definition only after confirming that the current procedure is the version you intend to undo. A newer accepted release needs protection from an older undo package. Record definition hashes and deployment identity in a real process so the current-state check is explicit.

DECLARE @Definition nvarchar(max);
SELECT @Definition = DefinitionText FROM #ModuleBefore;
IF @Definition IS NULL THROW 50000,'A saved definition is required.',1;
SET @Definition = STUFF(@Definition, CHARINDEX(N'CREATE', @Definition), 6,
    N'CREATE OR ALTER');
IF EXISTS
(
    SELECT 1 FROM #ModuleBefore
    WHERE uses_ansi_nulls <> 1 OR uses_quoted_identifier <> 1
)
    THROW 50000,'Review the saved module creation settings.',1;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
EXEC sys.sp_executesql @Definition;

This example deliberately supports the captured ON settings rather than pretending every module uses them. The sqlcmd utility connects with QUOTED_IDENTIFIER OFF unless you pass -I, so the settings check refuses a lab procedure created that way. Restore the correct settings for another baseline through an approved script. Verify the procedure's behavior and caller permissions afterward. Matching stored text is necessary evidence, but it is not a complete application validation.

Capture Changed Rows in the Forward Transaction

A data fix needs the old values saved as part of the change. OUTPUT captures the before and after values into an ordinary audit table here. The lab has no triggers, so the captured inserted rowversion matches the completed update. Triggered production tables need an adjusted capture and verification design.

CREATE TABLE dbo.DeploymentUndoRows
(
    DeploymentID uniqueidentifier NOT NULL,
    ItemID int NOT NULL,
    OldAmount decimal(12,2) NOT NULL,
    NewAmount decimal(12,2) NOT NULL,
    NewStamp binary(8) NOT NULL,
    UndoneAt datetime2 NULL,
    PRIMARY KEY (DeploymentID,ItemID)
);
DECLARE @DeploymentID uniqueidentifier = 'D312EF5A-80B9-41EC-AB1E-48BD5BD04762';
SET XACT_ABORT ON;
BEGIN TRANSACTION;
UPDATE dbo.DeploymentItem
SET Amount = 12.00
OUTPUT @DeploymentID,deleted.ItemID,deleted.Amount,inserted.Amount,
       inserted.RowStamp
INTO dbo.DeploymentUndoRows (DeploymentID,ItemID,OldAmount,NewAmount,NewStamp)
WHERE ItemID = 1 AND Amount = 10.00;
COMMIT TRANSACTION;

Use an independent session for these transaction examples and a unique deployment identifier per accepted change. If the transaction fails, its audit capture must fail with it. Retain the accepted audit rows after reversal so the evidence does not vanish when the corrective operation finishes.

Protect Later Changes in a Data Undo Script

Match both the expected new value and the captured rowversion before writing the old value. That detects intervening changes, including an update that returned Amount to the same numeric value. Validate the entire accepted population inside the same protected transaction rather than silently undoing only whichever rows still happen to match.

DECLARE @DeploymentID uniqueidentifier = 'D312EF5A-80B9-41EC-AB1E-48BD5BD04762';
SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRANSACTION;
    IF EXISTS
    (
        SELECT 1 FROM dbo.DeploymentUndoRows AS a
        LEFT JOIN dbo.DeploymentItem AS t WITH (UPDLOCK,HOLDLOCK)
          ON t.ItemID = a.ItemID
        WHERE a.DeploymentID = @DeploymentID AND a.UndoneAt IS NULL
          AND (t.ItemID IS NULL OR t.Amount <> a.NewAmount
               OR t.RowStamp <> a.NewStamp)
    )
        THROW 50000,'Current data differs from the expected deployed state.',1;
    UPDATE t SET Amount = a.OldAmount
    FROM dbo.DeploymentItem AS t
    JOIN dbo.DeploymentUndoRows AS a ON a.ItemID = t.ItemID
    WHERE a.DeploymentID = @DeploymentID AND a.UndoneAt IS NULL;
    UPDATE dbo.DeploymentUndoRows SET UndoneAt = SYSUTCDATETIME()
    WHERE DeploymentID = @DeploymentID AND UndoneAt IS NULL;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

I test an intervening update deliberately during rehearsal and confirm that reversal refuses it. Coordinate concurrent deployment workers and protect the audit population itself in the production implementation. A saved old value is useful only when the undo knows whether restoring it would overwrite accepted newer work.

Rehearse Failure and Know Recovery Limits

Which change cannot be reversed from the evidence you saved? Dropped data without a retained copy, lossy type conversion, external side effects, and some irreversible business actions need a different recovery or compensation plan. A DROP TABLE statement cannot reconstruct its old rows because a matching CREATE statement exists nearby.

A tested undo script needs both successful reversal and explicit refusal cases. Test forward change, application checks, undo, and baseline checks on the restored copy. Also test blocked schema removal, missing capture, permission failures, and changed current values. Keep backup recovery available for cases the reversal cannot cover. Release readiness means the recovery route has been demonstrated for its stated scope, not merely named in the deployment checklist.

Record the expected post-undo definition and data checks before running the rehearsal. Compare affected values, preserved unrelated rows, and application responses after reversal. Rowversion itself advances during the correction, so baseline equality does not mean every internal metadata byte returns to its earlier value. Restore business values and the accepted schema contract while documenting those legitimate differences. Keep the rehearsal result with the exact forward and undo versions. A later edit then cannot inherit a success claim from an earlier rehearsal.

Related reading on this blog: Automating SQL Server Deployments Across Multiple Databases Using Python and Undo Human Errors in SQL Server: SQL in Sixty Seconds #109: Point in Time Restore.

An undo script is not a promise that every change is reversible, it is a tested reversal for a defined and verified state.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.