Selectively Running Database Project Post Deploy SQL Scripts using SQLCMD

Posted by & filed under Development, Microsoft, SQL Server, TFS.

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).

The Problem

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.

The Solution

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).

Project Properties SQLCMD Variables

 

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.

PostDeployScript

 

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).

Publish Settings

 

That’s It!

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.

About Chris Pont


Chris is a UK based Web Developer / Technical Architect with Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified Professional Developer (MCPD) status and 8 years experience with large web applications using .NET, SQL Server and WCF.

Leave a Reply