SQL Development, Database Administration

Standardizing Idempotent Database Deployment Scripts

July 30, 2026

I’ve lost count of how many times I’ve had to run a developer’s script during a deploy that only had one chance to succeed. I’m sitting there sweating, hoping it doesn’t blow up the server, knowing full well that if it does, I’m the one who didn’t write the damn thing but still has to pay the price. At my company I usually get 30 minutes to an hour to do a quick review, then I slap together a new, never-tested wrapper that checks “if this script name has already been run, don’t run it.” Sounds smart on paper. In practice it still fails sometimes—especially when we’re updating development—and I’m just tired of the uncertainty.

I still remember one of those “good lord I was lucky that wasn’t me” deploys. Another DBA was pushing a 1,000-line schema update. The changes were out of order. It blew up a server that was using replication… and it didn’t just take down one server. It took down two.

That’s why I’ve become a cult-like advocate of idempotent scripts.

An idempotent script can be run multiple times and it’ll always leave the database in the same final state. Run it once, run it five times, interrupt it and restart it—same result, no errors, no surprises. That’s the opposite of most scripts I still see floating around.

This works perfectly the first time:

SQL

			
CREATE TABLE dbo.Customers (
    Id   INT            NOT NULL PRIMARY KEY,
    Name NVARCHAR(100)  NOT NULL
);

Run it again and it blows up. Or worse, it succeeds on one replica and fails on another after a failover. Now you’re stuck cleaning up under pressure.

Here’s the version that doesn’t do that to you:

SQL

			
IF NOT EXISTS (
    SELECT 1 
    FROM sys.tables 
    WHERE name = N'Customers' 
      AND schema_id = SCHEMA_ID(N'dbo')
)
BEGIN
    CREATE TABLE dbo.Customers (
        Id   INT            NOT NULL PRIMARY KEY,
        Name NVARCHAR(100)  NOT NULL
    );
END

		

You can also write the same check more concisely with OBJECT_ID:

SQL

			
IF OBJECT_ID(N'dbo.Customers', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.Customers (
        Id   INT            NOT NULL PRIMARY KEY,
        Name NVARCHAR(100)  NOT NULL
    );
END

		

Same idea works for almost every object type.

Availability Groups make this worse. The package runs on the primary, something interrupts it, failover happens, and the new primary is left in a half-changed state. Schema changes still have to be redone on the secondary by the redo thread. If the primary script fails or gets interrupted while that redo is still catching up, you can end up with divergent states across the replicas. Sometimes the changes happen so fast that the script can fail while the AG is still syncing the data. Re-running the original script then fails because objects already exist (or don’t exist the way the script expects). Suddenly you’re doing emergency cleanup at 2 a.m. instead of a clean, repeatable deployment.

Even without Availability Groups the same problems show up in CI/CD pipelines and any environment where scripts get retried.

Here’s what I expect to see in every deployment script these days:

For tables, indexes, and constraints—check the catalog views first (sys.tables, sys.indexes, sys.key_constraints, etc.) or use the shorter OBJECT_ID form shown above.

For stored procedures, views, functions, and triggers—use CREATE OR ALTER. This has been available since SQL Server 2016 SP1 and is the preferred modern approach for programmability objects because it avoids the drop-and-recreate dance and preserves permissions:

SQL

			
CREATE OR ALTER PROCEDURE dbo.GetCustomer
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT Id, Name
    FROM dbo.Customers
    WHERE Id = @CustomerId;
END

		

For columns—check sys.columns before you add anything:

SQL

			
IF NOT EXISTS (
    SELECT 1 
    FROM sys.columns 
    WHERE object_id = OBJECT_ID(N'dbo.Customers')
      AND name = N'Email'
)
BEGIN
    ALTER TABLE dbo.Customers
    ADD Email NVARCHAR(255) NULL;
END

		

Data changes should use MERGE or carefully written IF NOT EXISTS / UPDATE patterns so the script doesn’t insert the same rows twice or die on unique constraints.

Not every change can be made perfectly idempotent. Big data movements, certain index rebuilds, or complex dependency chains sometimes force you to accept one-time scripts. That’s fine—just document them clearly and treat them as one-time operations with solid pre-checks and post-validation. The goal is to make the vast majority of your deployment surface re-runnable so the risky exceptions become rare instead of routine.

A good deployment script should be safe to re-run, produce the same end state every time, fail fast and clearly if there’s a real conflict, work the same way in dev, test, and production, and play nice with CI/CD tools that retry failed steps.

I also recommend making this a required checkbox in code review. “Is this script idempotent?” The first few times it feels pedantic. After the first production incident it prevents, nobody complains anymore.

Non-idempotent scripts assume a perfect world: the script runs exactly once, nothing interrupts it, and no failover occurs. SQL Server environments—especially Availability Groups—are not perfect worlds.

Making scripts idempotent is one of the highest-ROI changes a DBA can push for. It’s not flashy. It doesn’t require new tools. It just stops a whole class of deployment failures before they start.

Start requiring it on every new script. You’ll thank yourself the next time a deployment needs to be retried at 2 a.m.

References

Standardizing Idempotent Database Deployment Scripts

Share this: