Pre-deployment and post-deployment scripts
Published 12 August 2019
Pre-deployment and post-deployment scripts are run before (pre-deployment script) or after (post-deployment script) the main block of scripts to be run against a database. Unlike a migration script, a pre-deployment or post-deployment script is run every time a project is deployed. These can be used for creating the database if it doesn't exist, altering database settings, or for static data .
SQLCMD syntax
Unlike migration scripts and programmable objects, pre/post-deployment scripts support SQLCMD syntax. This allows you to include scripts.
Deployment
When deploying a SQL Change Automation project, a deployment script is generated. The pre-deployment scripts are run before any migrations on each deployment and the post-deployment scripts are run after.
Pre-deployment and post-deployment scripts are also executed locally on your development database whenever an apply changes action is performed in SQL Change Automation.
Deployment order
Deployment order is determined by alphabetical ascending order on the file path, taking into account both folder path and file name. To adjust the order of deployment, change the file name.
Transaction handling
By default all pre-deployment and post-deployment scripts do not run within a transaction. If you wish to use a transaction within your pre/post-deployment scripts, you can perform a BEGIN TRAN yourself, however please ensure that you perform a COMMIT or ROLLBACK at the end of your script to avoid having overlapping transactions. See transaction handling for more information.
Controlling execution across different environments
If you need to have some more control on how these scripts are executed across different environments you can use variables. These can allow you to run certain statements only on certain environments.
Example pre and post deployment scripts
These scripts cover common use cases and give you examples of how to work with SQLCMD scripting variables with TSQL.
