By: Armando Prato
Overview
When databases are created or upgraded, data may need to be added, changed, or deleted. Moreover, certain actions may have to occur on the database before and/or after the process completes. Pre and Post Deployment scripts can be used to accomplish this.
Explanation
In our SSDT SQL project, we've defined a new table but we need a way to insert inital seed data into it. This can be accomplished by creating a PostDeploy script. In the Solution Explorer, right click on the Futbol project and choose Add->Script.
You will again then be presented with the Add New Item dialog where you can choose the type of script you wish to create. Here you can choose to create a PreDeploy script, a PostDeploy script, or loose scripts that are checked for syntax/object errors when a build occurs (Script (Build)) or not checked when a build occurs (Script (Not in Build)). Both the pre and post deployment scripts are SQLCMD aware which means you can create separate scripts within the Solution Explorer and include them directly into the main Script.PostDeployment.sql (or Script.PreDeployment.sql) script using the :r command of SQLCMD. This allows you the freedom to isolate specific tasks to specific scripts as opposed to maintaining one monolithic script.
Choose Post-Deployment Script and click Add.
You will then be presented with a script template that you can fill in. Here, I enter a couple of seed data entries and save my script.
Note the conditional logic. Pre and post deployment scripts always run irrespective of whether we're installing or upgrading. It's prudent to make your scripts idempotent (or re-runnable) so a refactor of your database doesn't fail.
When a database publish occurs, 3 blocks of code are created within a central .sql script which is generated by Visual Studio. This final auto generated script is what is ultimately deployed to the database. This script has three main blocks of code produced in this order:
- All code that makes up Scripts.PreDeployment.sql
- Auto generated code representing database schema creates/drops/changes based on the contents of the project
- All code that makes up Scripts.PostDeployment.sql
Additional code may also be added to this main script by Visual Studio depending on what deployment options that were chosen such as constraint checking.
You may be wondering how a pre deployment script could be useful. One situation to consider is a complex migration of data. Using the pre and post deployment scripts, you could perform some up front work prior to deployment occurring and then work on that data after the deployment occurs.
