SSDT部署后脚本 [英] SSDT Post Deployment Scripts

问题描述

我想在部署后忽略脚本。将分支特定的后期部署脚本部署到SSDT的生产环境中后，如何归档/删除特定于分支的后期部​​署脚本？周围是否有最佳做法？

I would like to ignore post deployment scripts after it has been deployed. How do you archive/remove a branch specific post deployment script after it has been deployed on production environment in SSDT? Are there any best practices around?

推荐答案

我通常要做的是创建日志表并存储所有已执行的脚本。这是表结构：

What I used to do is to create log table and store all the executed scripts. This is the table structure:

CREATE TABLE dbo.publish_script_logs
(
    script_name_id VARCHAR(255) NOT NULL
  , database_name  VARCHAR(255) NOT NULL
  , execution_time DATETIME2(7) NOT NULL
);

然后我们创建了以下脚本文件夹结构：

Then we created following scripts folder structure:

one_time_scripts
  initial_data_insert.sql
  ...
  postscript_all_together.sql
  prescript_all_together.sql
  ...
Script.PostDeployment1.sql
Script.PreDeployment1.sql

其中 initial_data_insert.sql 是您所需的脚本，应该只在环境中执行一次，而 pre\postscript_all_together.sql 是其中所有这些脚本收集在一起。必须为所有这些脚本设置 Build = None 。 有限制-一次性脚本中不允许使用GO语句分隔符。

where initial_data_insert.sql is your needed script that is supposed to be executed on environment just once and pre\postscript_all_together.sql are the scripts where all these scripts are collected together. Build = None must be set for all of these scripts. There is limitation - GO statement separator is not allowed in "one time scripts".

现在这是这两个脚本的内容对于单个脚本：

Now this is what will these 2 scripts will have inside for single script:

:SETVAR ScriptNameId ".\initial_data_insert"
GO
IF NOT EXISTS ( SELECT  *
                FROM    [dbo].[publish_script_logs]
                WHERE   [Script_Name_Id] = '$(ScriptNameId)' 
                AND [database_name] = DB_NAME()
                )
BEGIN
BEGIN TRY
    :r $(ScriptNameId)".SQL"
    INSERT  INTO [dbo].[publish_script_logs]
    VALUES  ( '$(ScriptNameId)', DB_NAME() ,GETDATE() );
END TRY
BEGIN CATCH
    DECLARE @err VARCHAR(MAX) = ERROR_MESSAGE();
    DECLARE @msg VARCHAR(MAX) = 'One time script $(ScriptNameId).sql failed ' + @err;
    RAISERROR (@msg, 16, 1);
END CATCH
END;
GO

最后在 Script.PostDeployment1.sql 和 Script.PreDeployment1.sql 文件，您将拥有：

And finally in the Script.PostDeployment1.sql and Script.PreDeployment1.sql files you'll have:

：r .\one_time_scripts\postscript_all_together.sql

和

：r .\one_time_scripts\prescript_all_together.sql

这篇关于SSDT部署后脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！