How to: Specify Predeployment or Postdeployment Scripts
Pre-deployment and post-deployment scripts execute Transact-SQL statements before and after the main deployment script, which is generated from the database project. The pre-deployment script will not be executed when updating targets from schema compare results in Visual Studio. A project can have only one pre-deployment and one post-deployment script. These scripts can be used for many purposes. For example:
A pre-deployment script can copy data from a table that is being changed into a temporary table before re-formatting and applying the data to the changed table in a post-deployment script,
You can insert reference data that must exist in a table in a post-deployment script. Before you insert the data, you can test whether the table already contains data. If the table is not empty, you must clear the existing data or specify that you want to always re-create the database before you deploy it. You can add a statement such as the following to your post-deployment script:
IF (EXISTS(SELECT * FROM [dbo].[MyReferenceTable])) BEGIN DELETE FROM [dbo].[MyReferenceTable] END
To add and modify a pre- or post-deployment script
In Solution Explorer, expand your database project to display the Scripts folder.
Right click on the Scripts folder and select Add.
Select Scripts in the context menu.
Select Pre-Deployment Script or Post-Deployment Script. Optionally, specify a non-default name. Click Add to finish.
Double click the file in the Scripts folder.
The Transact-SQL editor opens, displaying the contents of the file.
You can use SQLCMD syntax and variables in your scripts and set these in the database project properties. For example:
You can use SQLCMD syntax to include the content of a file in a pre- or post-deployment script. Files are included and run in the order you define them:
You can use SQLCMD syntax to reference a variable in the post-deployment script. You set the SQLCMD variable in the project properties or in a publish profile:
:setvar TableName MyTable SELECT * FROM [$(TableName)]
For more information on using SQLCMD in scripts see Database Project Settings.