Today I was working on a task that would allow us to deploy database project from team build. We are using TFS 2008, by the way. The reason I needed this functionality is because we wanted to run unit and integration tests as part of build as well as ensure that changes made to database project are valid. With this goal established, we set to setup MSBuild project to do so.
First I scoured the internet for setup steps on MSDN. There were a few articles, but nothing we tried worked. I had to continue searching until finally found the correct combination of many steps to make it work.
I just opened the build project file in Visual Studio and edited it. The file is called TFSBuild.proj and describes our continuous integration build in our case. I also had to setup SQL Server to deploy database to and made sure that I have an account I can use to deploy the database. Then I created connection string and added it to the PropertyGroup section just before </Project> tag of the project file.
<!– TEST ARGUMENTS
If the RunTest property is set to true, then particular tests within a
metadata file or test container may be specified here. This is
equivalent to the /test switch on mstest.exe.
<TargetConnString>Data Source=ServerName%3BUser ID=myUser%3BPassword=myPassword</TargetConnString>
If you look at the connection string, it does look strange. The problem is that we have to encode it for MS Build not error out. You could get a variety of errors, such as “Data Source is not a valid property, etc…” So, you have to replace spaces in your connection string with   and semicolons with %3B. Once this is done, the connection string is ready. The next step is to setup a MS Build task. It goes into Target section of AfterDropBuild type just before end of the file.
<Message Text="Starting to deploy database project"/>
<Message Text="Finished deploying database project"/>
Let’s examine the task. We are printing a couple of messages to make it easier to trace in the log file. Then we have MSBuild task to deploy the database. This Deploy target by default will also build the database project. Then I am setting target database name – DB_NAME, then I am setting the location to script file that database project’s build process generates, then I set the connection string, pointing to the setting in the property group from the previous step, and lastly I set the crucial property – DeployToDatabase to true.
That is all. Now my continuous integration build will ensure that my database can successfully deploy and prepare the environment to run integration tests.