Monday, July 25, 2011

VS 2010 Database Projects Post Deployment Scripts not Executing

Today I have been dealing with a really frustrating problem when working the DB Projects in Visual Studio 2010.

One really great feature is the pre/post-deployment scripts that you can execute on each deploy for things like static or reference data.  Or in some cases some test data as well not run by a data generation plan.

My issue was whenever I'd click deploy, the DB would be recreated, my tables I updated were created as expected, but it would not execute the post deployment file.  After some help from a co-worker, he diagnosed and found the problem was with the Build Action property on the file itself.

To correct, select the Script.PostDeployment.sql file in the Solution Explorer.  Click F4 if necessary to display the property grid.  Click Build Action, choose PostDeploy.  Deploy away.

Hope this saves you a few frustrating hours.

One note, you may only have 1 post deployment file in your project, use SQLCMD :r syntax to run all the scripts you want to execute post deploy.