Working With Pre and Post Deployment Scripts




By:

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

updating the Script.PostDeployment.sql script. In the Solution Explorer

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.


Last Update: 9/10/2011





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Thursday, February 28, 2019 - 7:26:19 AM - shivendra Back To Top

 how to decide which script to keep in predeployment script and which to keep in post deployment script?



download


get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools