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. Deployment scripts can be used to accomplish this.
Explanation
In our database project, we've defined a new table but we need a way to insert inital seed data into it. This can be accomplished by updating the Script.PostDeployment.sql script. In the Solution Explorer, we double click this file and modify it as shown
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 deployment 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.
Both the pre and post deployment scripts are SQLCMD aware which means you can create separate scripts within the Solution Explorer under it's Pre and Post Deployment folders and include them directly into the main Script.PostDeployment.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. SQLCMD variables can be created inside Database.sqlcmdvars within the Solution Explorer.
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.
