post deployment script to run a script only one time

Question

If I want to have the post deployment script run a script only one time is there a way to do this?

I want to run post deployment scripts only once for my database

Answer 1

When working with one-time scripts, you can use the Script.PostDeployment.sql and Script.PreDeployment.sql files to execute the scripts before and after deployment respectively. To keep track of which scripts have been executed, you can create a history table to record the script name, the database name, and the execution time.

In your Script.PostDeployment.sql file, you can use the following code to execute the one-time script:

:SETVAR ScriptNameId ".\PostScripts\PathToScript\ScriptNameHere"
GO

IF NOT EXISTS (   SELECT *
                    FROM dbo.ScriptsHistoryTableWithVeryNiceName
                   WHERE Script_Name_Id = '$(ScriptNameId)' AND database_name = DB_NAME())
    BEGIN
        BEGIN TRY
            :r $(ScriptNameId)".SQL"
            INSERT INTO dbo.ScriptsHistoryTableWithVeryNiceName
            VALUES ('$(ScriptNameId)', DB_NAME(), SYSDATETIME());
        END TRY
        BEGIN CATCH
            DECLARE @err VARCHAR(MAX) = ERROR_MESSAGE();

            RAISERROR('One time script $(ScriptNameId).sql failed %s', 16, 1, @err);
        END CATCH;
    END;
GO

In this code, you can specify the path to the one-time script by setting the ScriptNameId variable. If the script has not been executed before, the script file will be executed, and its details will be added to the history table.

Here's the code to create the dbo.ScriptsHistoryTableWithVeryNiceName table:

CREATE TABLE dbo.ScriptsHistoryTableWithVeryNiceName
(
    ScriptNameId  VARCHAR(255) NOT NULL
  , DatabaseName  sysname NOT NULL
  , ExecutionTime DATETIME2(7) NOT NULL
);
GO

IMPORTANT: you cannot include a GO statement within the one-time script file itself, as this will cause the outer script to be prematurely terminated.