Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
SQL database in Microsoft Fabric
Pre- and post-deployment scripts are SQL scripts that are included in the project to be executed during deployment. Pre/post-deployment scripts are included in the .dacpac but they aren't compiled into or validated with database object model. A pre-deployment script is executed before the deployment plan is executed but the deployment plan is calculated before the script executes. A post-deployment script is executed after the deployment plan completes.
SQL project file sample and syntax
A SQL project file can have a single pre-deployment script and a single post-deployment script specified.
The following example from a SQL project file adds the file prep-db.sql as a pre-deployment script.
...
<ItemGroup>
<PreDeploy Include="prep-db.sql" />
</ItemGroup>
The following example from a SQL project file adds the file populate-app-settings.sql as post-deployment script.
...
<ItemGroup>
<PostDeploy Include="populate-app-settings.sql" />
</ItemGroup>
</Project>
Multiple files can be executed as part of a pre- or post-deployment script by using a SQLCMD script that calls each file in order.
:r .\scripts\script1.sql
:r .\scripts\script2.sql
Those files should be excluded from the database model build by setting the Build Action property to Remove in the file properties in Visual Studio or by adding an entry for the file in the .sqlproj file with the Build attribute set to Remove. When the SQL project is built, the additional files are combined into their referencing pre-deployment or post-deployment script in the .dacpac by the Microsoft.Build.Sql project SDK.
...
<ItemGroup>
<Build Remove="scripts\script1.sql" />
<Build Remove="scripts\script2.sql" />
</ItemGroup>
</Project>
Tip
You can validate the pre-deployment and post-deployment scripts after project build, by changing the .dacpac file extension to .zip and unarchiving the .zip to a folder. A single .sql file is present for pre-deployment and post-deployment scripts, and should contain the entire Transact-SQL contents of all referenced files in the originating SQL project.
Add pre- and post-deployment scripts
In Solution Explorer, right-click the project and select Add > Script. Select Pre-Deployment Script or Post-Deployment Script.
The script file is added to the project and opened in the query editor, where you can complete the script. This script will be executed before or after the deployment plan is executed every time the project is deployed.
In Solution Explorer, right-click the project node and select Add, then New Item. The Add New Item dialog appears, select Show All Templates. and then Table. Select Pre-Deployment Script or Post-Deployment Script.
The script file is added to the project and opened in the query editor, where you can complete the script. This script will be executed before or after the deployment plan is executed every time the project is deployed.
In the Database Projects view of VS Code or Azure Data Studio, right-click the project and select Add Pre-Deployment Script or Add Post-Deployment Script. Provide a script name without the file extension.
The script file is added to the project and opened in the query editor, where you can complete the script. This script will be executed before or after the deployment plan is executed every time the project is deployed.
Edit the .sqlproj file directly to add pre- or post-deployment scripts. Add a <PreDeploy> or <PostDeploy> item to the <ItemGroup> section of the .sqlproj file.
For example, to add the script scripts\before-script.sql to our project as a pre-deployment script:
...
<ItemGroup>
<PreDeploy Include="scripts\before-script.sql" />
</ItemGroup>
This script scripts\before-script.sql is executed before the deployment plan is executed every time the project is deployed.