SSDT 部署后脚本
我想在部署后忽略部署后脚本.在 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 是您需要的脚本，应该只在环境中执行一次，而 prepostscript_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 prepostscript_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_scriptspostscript_all_together.sql
和
:r .one_time_scriptsprescript_all_together.sql
相关文章