Idempotent Deployment Scripts: Safe to Run Twice

A release stops halfway, and the next attempt fails because the first table already exists. Idempotent deployment scripts let you resume safely by checking the state each operation is meant to establish.

Define What a Second Run Should Preserve

Idempotence means repeated execution reaches the same intended state. It requires more than catching an object-already-exists error. A table with the right name and the wrong columns is still wrong. Define the expected schema, values, and release record before deciding that an existing object lets you skip work.

I separate repeatable definitions from one-time data transformations. A procedure definition can be reapplied to establish the current text. A price adjustment or status conversion needs a guard against repeating its business effect. Those two tasks belong in different parts of the deployment contract.

Use a dedicated scratch database for the examples. The object names below are reserved for this demonstration. Do not run them against existing application objects with similar names. Schema changes still acquire locks and need a planned execution window even when their repetition is safe.

Idempotent Deployment Scripts Check Catalogs First

Use sys.tables with the schema identifier to locate a table. Check sys.columns before adding a column and sys.indexes before adding an index. Scope each lookup to the intended object. Matching a name elsewhere in the database does not establish that the required object exists here.

IF NOT EXISTS
    (SELECT 1 FROM sys.tables
     WHERE schema_id = SCHEMA_ID(N'dbo') AND name = N'AppSetting')
BEGIN
    CREATE TABLE dbo.AppSetting
    (
        SettingID int NOT NULL PRIMARY KEY,
        SettingValue varchar(30) NOT NULL
    );
END;
IF NOT EXISTS
    (SELECT 1 FROM sys.columns
     WHERE object_id = OBJECT_ID(N'dbo.AppSetting')
       AND name = N'IsEnabled')
BEGIN
    ALTER TABLE dbo.AppSetting
    ADD IsEnabled bit NOT NULL
        CONSTRAINT DF_AppSetting_IsEnabled DEFAULT (1) WITH VALUES;
END;
IF NOT EXISTS
    (SELECT 1 FROM sys.indexes
     WHERE object_id = OBJECT_ID(N'dbo.AppSetting')
       AND name = N'IX_AppSetting_Value')
BEGIN
    CREATE INDEX IX_AppSetting_Value ON dbo.AppSetting(SettingValue);
END;

The guards prevent duplicate creation in a serialized deployment. They do not prove the definitions match. Inspect type, nullability, default, keys, included columns, and filters where relevant. Stop on unexpected differences instead of silently accepting them. An existing object is evidence to inspect, not a free pass.

Reapply Module Definitions Deliberately

CREATE OR ALTER handles either a missing module or an existing one. It keeps deployment text simpler than separate create and alter branches. The statement still needs its own batch. A following call belongs after GO, so the parser does not absorb it into the module definition.

GO
CREATE OR ALTER PROCEDURE dbo.ReadAppSetting
AS
BEGIN
    SET NOCOUNT ON;
    SELECT SettingID, SettingValue, IsEnabled
    FROM dbo.AppSetting
    ORDER BY SettingID;
END;
GO
EXEC dbo.ReadAppSetting;

Reapplying a definition can change metadata such as modification time even when the business behavior stays identical. Account for that in auditing. If the release requirement forbids that metadata change, compare the accepted definition before executing it. Do not claim every repeated DDL statement performs literally no work.

Guard Data Fixes in Idempotent Deployment Scripts

A targeted data correction should name both the identity and the old value. That prevents overwriting a later valid business change when the script is replayed. Handle an unexpected current value explicitly if the release cannot proceed without the correction. A broad UPDATE is a poor retry mechanism.

IF NOT EXISTS (SELECT 1 FROM dbo.AppSetting WHERE SettingID = 1)
    INSERT dbo.AppSetting (SettingID, SettingValue) VALUES (1, 'Pending');
UPDATE dbo.AppSetting
SET SettingValue = 'Ready'
WHERE SettingID = 1 AND SettingValue = 'Pending';
SELECT SettingID, SettingValue, IsEnabled
FROM dbo.AppSetting
WHERE SettingID = 1;

This correction establishes a value instead of incrementing or multiplying it repeatedly. For more complex migrations, specify the accepted starting states and expected affected identities. Keep before-values in the release evidence where recovery requires them. A version row alone cannot reconstruct overwritten business data.

Record One-Time Changes Transactionally

Create a release ledger with a unique script identifier. Apply the data change and record its completion in one transaction. That prevents a successful marker from surviving a rolled-back change. It also prevents a completed change without its marker when the same transaction commits.

IF OBJECT_ID(N'dbo.ReleaseLedger', N'U') IS NULL
    CREATE TABLE dbo.ReleaseLedger
    (ScriptID varchar(80) NOT NULL PRIMARY KEY,
     AppliedUTC datetime2(7) NOT NULL);
GO
CREATE OR ALTER PROCEDURE dbo.ApplyAppSettingFix
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    IF @@TRANCOUNT <> 0 THROW 51000, 'Use a session without a transaction.', 1;
    BEGIN TRY
        BEGIN TRANSACTION;
        DECLARE @LockResult int;
        EXEC @LockResult = sys.sp_getapplock
            @Resource = N'SettingDeployment',
            @LockMode = 'Exclusive', @LockOwner = 'Transaction',
            @LockTimeout = 10000;
        IF @LockResult < 0 THROW 51001, 'Deployment lock was unavailable.', 1;
        IF NOT EXISTS (SELECT 1 FROM dbo.ReleaseLedger
                       WHERE ScriptID = 'SettingFix-001')
        BEGIN
            UPDATE dbo.AppSetting SET SettingValue = 'Complete'
            WHERE SettingID = 1 AND SettingValue = 'Ready';
            IF NOT EXISTS (SELECT 1 FROM dbo.AppSetting
                           WHERE SettingID = 1 AND SettingValue = 'Complete')
                THROW 51002, 'The expected setting state was not found.', 1;
            INSERT dbo.ReleaseLedger VALUES
                ('SettingFix-001', SYSUTCDATETIME());
        END;
        COMMIT;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK;
        THROW;
    END CATCH;
END;
GO

The application lock serializes callers that use this deployment protocol. It does not stop unrelated writers from changing the setting. Coordinate deployments with application activity. Run the ledger creation and schema phase through the same serialized release process rather than letting several setup sessions race.

Exercise the Retry Without Inventing Results

I test both a clean run and a retry after a controlled failure. The following calls run the guarded data phase twice. Inspect the setting and ledger after each call. The second call should leave the completed value and original ledger timestamp intact. That is the repeat-run property this example verifies.

EXEC dbo.ApplyAppSettingFix;
SELECT * FROM dbo.AppSetting;
SELECT * FROM dbo.ReleaseLedger;
EXEC dbo.ApplyAppSettingFix;
SELECT * FROM dbo.AppSetting;
SELECT * FROM dbo.ReleaseLedger;
DROP TABLE IF EXISTS #DeploymentScratch;

DROP IF EXISTS is useful for a deliberately disposable helper. It is not authorization to remove application data. Test failure handling before the ledger insertion as well as afterward. A release that retries cleanly only when nothing failed has not yet answered the important question.

Preserve the Release Identity and Failure Evidence

Give every one-time change its own immutable identifier. If the next release needs another correction, create another identifier. Editing a recorded script while keeping its old identity causes the ledger to skip a change that was never applied. Keep the accepted text with the deployment evidence outside the database.

The ledger timestamp describes when the transaction recorded completion. It does not prove every application server received the matching code release. Coordinate schema and application compatibility explicitly. For a staged rollout, support both accepted application versions until the old version has been retired through the planned process.

Check error paths with a deliberately unexpected setting value in a separate test copy. Confirm that the procedure throws, the transaction ends, and no completion marker appears. Restore the accepted starting state and retry. The resulting business state should match the clean-run state. Preserve those comparisons instead of claiming success from the lack of an error message.

A successful retry also needs the same permissions as the initial deployment. Test the actual release identity, including its access to metadata. Limited catalog visibility can make an existence check return misleading results. An administrator's test does not certify the account that performs the scheduled release.

Make Idempotent Deployment Scripts Fail Visibly

Which difference should stop this deployment rather than be repaired automatically? Write that decision into the checks. Compare definitions, permissions, and data invariants before accepting an existing state. Keep the accepted script immutable once its version is recorded. A changed script under an old identifier hides unfinished work.

Idempotent deployment scripts need ownership and a clear recovery contract. Keep errors visible, clean transactions, and retain the evidence needed to rerun safely. Idempotent deployment scripts become useful when the second attempt explains exactly what remains, instead of merely surviving duplicate object names.

Related reading on this blog: Automating SQL Server Deployments Across Multiple Databases Using Python and CREATE Statement in TRANSACTION.

A repeatable deployment is not a collection of ignored errors, it is a checked path to the intended state.

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