Writing Rerunnable Deployment Scripts for Database Changes

A deployment fails halfway, then the retry stops at the first CREATE. Rerunnable deployment scripts check the current schema before each step and record what completed. They make a second run explainable, which is exactly what the team needs during recovery.

Decide What a Second Run Should Do

Idempotent does not mean every command is harmless. A rerun should either confirm the intended state or make the remaining change. It should not drop valuable data to satisfy a neat script. I start by writing the expected state for each object: column type and nullability, index keys and includes, procedure definition, and version number. A name check alone can miss an object with the wrong definition.

Use a restored copy of the real database for the first rehearsal. A fresh empty database hides old constraints, unexpected data, and objects from partial deployments. What would happen if the script stopped after step three and was run again? Ask that before production does it for you.

Use CREATE OR ALTER for Modules

CREATE OR ALTER handles a procedure that does not yet exist and one that already exists. It keeps dependent permissions more predictably than dropping and recreating the module. The statement must be alone in its batch, so place GO before any test call. The sample is deliberately small; a real procedure needs its full reviewed body.

CREATE OR ALTER PROCEDURE dbo.usp_DeploymentStatus
AS
BEGIN
    SET NOCOUNT ON;
    SELECT DB_NAME() AS database_name,
           SYSDATETIME() AS checked_at;
END;
GO
EXEC dbo.usp_DeploymentStatus;

Rerunning the script creates or updates the procedure, then calls it. That does not prove that the new definition is correct. Compare the deployed module definition with the approved source and run its meaningful tests. Also check SET options captured at creation time. A module can be syntactically identical but behave differently under old settings.

Reserve DROP IF EXISTS for Disposable Objects

DROP IF EXISTS is useful for a staging table, temporary helper, or obsolete object when removal is explicitly part of the change. It is not a general replacement for understanding the current schema. Dropping a production table loses data and dependent objects. For disposable local work, the pattern is simple and repeatable.

DROP TABLE IF EXISTS #DeploymentStage;
CREATE TABLE #DeploymentStage
(
    item_id int NOT NULL PRIMARY KEY,
    status_code char(1) NOT NULL
);

For a permanent object, write a separate migration plan that protects data and checks dependencies. I treat DROP as a business decision, not a convenient way to clear an error message. The best rerun path is usually to detect a completed step and leave it alone.

Guard an Added Column by Its Real State

COL_LENGTH returns the byte length of an existing column and NULL when it is absent or metadata is not visible. Use it with a confirmed table and sufficient permissions. The example adds a nullable column only when missing, then checks its definition. An existing column with the wrong type is an error to resolve, not a reason to silently skip.

IF OBJECT_ID(N'dbo.Orders', N'U') IS NULL
    THROW 50001, 'Expected dbo.Orders table is missing.', 1;
IF COL_LENGTH(N'dbo.Orders', N'ExternalReference') IS NULL
    ALTER TABLE dbo.Orders
    ADD ExternalReference nvarchar(80) NULL;
SELECT c.name, t.name AS type_name, c.max_length,
       c.is_nullable
FROM sys.columns AS c
JOIN sys.types AS t ON t.user_type_id = c.user_type_id
WHERE c.object_id = OBJECT_ID(N'dbo.Orders')
  AND c.name = N'ExternalReference';

Replace the sample table and column with the actual change. If the verification row differs from the approved definition, stop the deployment. Do not keep moving because the column name exists. Existing data also needs a backfill and constraint plan if the final design requires NOT NULL.

Check Indexes by Definition, Not Only Name

An IF NOT EXISTS against sys.indexes prevents duplicate creation, but the name can exist with different keys or includes. Inspect sys.index_columns and the filter definition before declaring the index present. If a definition differs, decide whether to ALTER, rebuild, or create a new index under an approved name. The guard below shows the first step.

IF NOT EXISTS
(
    SELECT 1 FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Orders')
      AND name = N'IX_Orders_ExternalReference'
)
    CREATE INDEX IX_Orders_ExternalReference
    ON dbo.Orders (ExternalReference);

This code is not a complete index-definition validator. Record the intended key order, sort directions, includes, and filter in the deployment review. An old index with the same name can make the guard succeed while the workload still lacks the needed path. I also measure the create time and log growth on the restored copy.

Record Versions Inside Rerunnable Deployment Scripts

A small version table gives the team a durable answer to what ran. Create it once, then insert a row only after the change and verification succeed. Use a unique change ID, applied time, and a short description or script checksum. A transaction with XACT_ABORT ON can keep ordinary schema and version changes together, but some operations require separate batches or have special transaction rules. Design the unit of work around those limits.

IF OBJECT_ID(N'dbo.SchemaChangeLog', N'U') IS NULL
    CREATE TABLE dbo.SchemaChangeLog
    (
        change_id varchar(40) NOT NULL PRIMARY KEY,
        applied_at datetime2(0) NOT NULL,
        description nvarchar(200) NOT NULL
    );
IF NOT EXISTS
(
    SELECT 1 FROM dbo.SchemaChangeLog
    WHERE change_id = '2026-01-add-external-reference'
)
    INSERT dbo.SchemaChangeLog
        (change_id, applied_at, description)
    VALUES ('2026-01-add-external-reference',
            SYSDATETIME(), N'Added order external reference');

Insert that record only after the matching schema check, not merely because the script reached the line. A record without the intended object is worse than no record because it gives false confidence. If a retry sees the version row, verify the schema again before skipping work.

Rehearse Rerunnable Deployment Scripts on a Restored Copy

Run the script twice on a restored copy. Interrupt it deliberately between steps and run it again. Compare schema definitions and version rows after each pass. Test with old data that violates new constraints, and measure long index builds. Keep a rollback or forward-fix plan for steps that cannot be reversed easily.

I also save the exact script run, target database, start and finish time, and error text. A deployment log is useful only when it tells you where to resume. Rerunnable code reduces panic, but it does not excuse an unreviewed production change. The dry run is where surprises should happen.

Treat Data Changes in Rerunnable Deployment Scripts Separately

Schema guards are the easy part. A backfill must know which rows have already been converted, and a retry must not compound a value or issue the same external action twice. Use a stable row key and a predicate that selects only unfinished work. For a large table, update bounded batches, record progress, and verify counts at the end. If a script sends messages or calls an outside service, database transactions alone cannot undo those effects. Keep that work in a separate, explicitly repeatable phase.

Related reading on this blog: Automating SQL Server Deployments Across Multiple Databases Using Python and Generate Script of SQL Server Objects: SQL in Sixty Seconds #184.

A rerunnable script is not a repeated blind change, it is a change that checks state first.

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