Working with Pre & Post-Deployment scripts
Published 14 August 2018
What are Pre & Post-Deployment scripts?
Pre & Post-Deployment scripts (released in SQL Source Control version 6.3) contain SQL that is run during a deployment of a SQL Source Control project. This means you can have more control over your database deployments.
How do they work?
A SQL Source Control project can contain one Pre-Deployment script and one Post-Deployment script.
They are executed as part of the deployment of a SQL Source Control project when deploying from SQL Compare UI & command line (supported in version 13.4.7 and later), or SQL Change Automation (supported in version 3.0.4 and later), but not SQL Data Compare.
When deploying a SQL Source Control project, a deployment script is generated based on the differences between the database state represented by the project and the target database. The Pre-Deployment script is executed before the section of the script that contains the schema changes generated by SQL Compare, and the Post-Deployment script is executed after.
Pre & Post-Deployment scripts are also executed locally on your development database whenever a Get latest or Undo action is performed in SQL Source Control.
The scripts will run every time in the context of the database deployed to, regardless of environment.
Requirements
Due to the way the scripts are executed, there are a number of requirements to keep in mind when writing them:
- The script must be re-runnable and idempotent, as it is executed as part of every deployment.
- For example, any object creation statements must be executed inside object existence checks.
- The script must be able to run in all environments.
- For example, if the deployment could be to a Microsoft Azure SQL Database, the script can only use supported syntax. It must also support any possible differences in database collation across your database environments.
- The script is executed outside of the transaction that makes up the majority of the deployment script, so you may want to add transaction handling to the script.
- Error handling should be included in the script. This is because the script will not abort execution on error.
- If an error occurs before the Post-Deployment script,
NOEXECwill beON. Therefore if you want a Post-Deployment script to execute you'll have to setNOEXECtoOFF.
- If an error occurs before the Post-Deployment script,
Limitations
There are some limitations to consider when using Pre & Post-Deployment scripts.
- SQLCMD syntax and variables are not supported.
- If objects are created as part of the scripts, the SQL Compare Engine may duplicate these object creations. This can be mitigated by filtering those objects.
- See Exclude objects using filters for more information about using filters.
- When deploying a SQL Source Control project, if the only change is to a Pre or Post-Deployment script then a deployment may not be possible as a specific object needs to be deployed.
- There is no SQL parsing before the scripts are committed, so errors will only be identified when the deployment script is executed (eg, when you Get latest in SQL Source Control or deploy with SQL Compare, or it is executed as part of a continuous integration process with SQL Change Automation).
