We’ve been playing with our TFS builds recently now have a system in place where our nightly build will drop and recreate the project’s databases based on the schemas in the database projects. As part of the publish, MSBuild will generate a script file containing either the entire schema (nightly build) or any schema changes found in running a comparison (QA environment and higher).
One issue we had was that we wanted a post deploy script to run in any data required for testing the nightly environment (users, reference data, even a few test customer records). Fine, database projects handle this, simply mark the script file as Build: Post Deploy Script and the script will run after the schema changes have been run in. Only one problem, this post deploy script will run regardless of environment. On our QA environment, we already had the data we needed there, since we weren’t dropping the database, just running in the schema changes.
I found a stack overflow article here – http://stackoverflow.com/q/7151021/503734 – which talks about changing the sqlproj file to copy certain files into a location that the rest of the build is looking for to run as a post deploy script. A bit messy. Here’s how I eventually handled it…
Add a SQLCMD Variable
Right click on the sqlproj in your solution and go to properties. You’ll see a SQLCMD Variable tab. Add a variable, something like $(EXECUTEPOSTDEPLOY).
Add a Condition to your Post Deploy Script
Check the $(EXECUTEPOSTDEPLOY) variable, and if it’s not equal to ‘True’, SET NOEXEC ON. This basically turns of execution of scripts for that connection. You may want to SET NOEXEC OFF again at the end if you have other scripts to run.
Set the SQLCMD Variable depending on configuration
In your publish profile for your required configuration, set the SQLCMD Variable to either true or false (depending on whether you want to run the script or not).
Run your build (with publish enabled, obviously!) and it will either run or not run depending on your setup. You can set other SQLCMD Variables and play around with the conditions to allow the script to run. You could also SET NOEXEC on or off at various points to selectively run parts of the script (would probably be better to split into seperate scripts if this is the case though).
Hope this helps.