Azure DevOps - how to execute Pre and Post Deployment SQL scripts

Question

In Azure DevOps release pipeline, How do I get our Script.PreDeployment.sql and Script.PostDeployment.sql files to execute during our SQL Server database deploy task?

In our release pipeline we have a Database Deployment phase, which has a SQL Server database deploy task. The tasks publishes our DACPAC file just fine. However I cannot figure out how to get the pre and post deployment scripts to execute.

Both of the scripts are included in the project, both have the appropriate BuildAction set to PreDeploy and PostDeploy. Yet in the logs of the dacpac deployment, there is no indication that the files were run - I have a bunch of PRINT statements in there.

Answer 1

I am also working on the post deployment scrip by using SSDT approach, and for deployment I am using the Azure SQL DacpacTask task in my Azure Pipeline, so you just need to create the post deployment scripts as you can see this in image and save it, after you run the Azure build this will add it in the Azure Pipeline artifact ill automatically executed in above azure tasks under release pipeline. First it will executed the database deployment and after that it runs the post deployment script. It works for me .

Answer 2

You can make use of Command line task to run those pre and post deployment SQL script using the SQLCMD tool.

The arguments to this its execution script are:

-S {database-server-name}.database.windows.net 

-U {username}@{database-server-name} 

-P {password} 

-d {database-name} 

-i {SQL file}

If you store the pre/postdeployment script in artifact, you can specify -i as like $(System.DefaultWorkingDirectory)/drop/Script.PreDeployment.sql.