How To: Deploy SQL Database Project using Team System Build 2010 (beta 2)

Question

Can anyone shed some light on how to get Team Build 2010 beta 2 to push a SQL database project to the SQL server?

In VSTS 2008 you'd just add MSBuild commands with the targets attribute set to "deploy" in the TFSBuild.proj file, but I'm having a little trouble translating that to the new workflow based xaml thing that 2010 uses.

What I'm looking for is how to trigger the actual deployment of the databases themselves. It is already generating the deployment scripts just fine.

Answer 1

There is another solution to this problem if you don't need incremental database upgrade. So if recreating the database for each build is ok with you the following would also work.

Add a deploy target to Database Project file

Configure the deployment settings for ‘My project settings’. Those settings will then be used by the build server when building the solution. When building locally, the settings used will be the ones from ‘My isolated development environment’.

1. In the database project properties

Deploy Action needs to be ‘Create a deployment script (.sql) and deploy the database’; this will prevent the execution of the script, it will only create it.

1. Database project file

Modify the database project file (right-click database project, select Unload, right-click again, select Edit [ProjectName].dbproj) from

<Project DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003" ToolsVersion="4.0">

To

<Project DefaultTargets="Build;Deploy" xmlns="http://schemas.microsoft.com/developer/msbuild/2003" ToolsVersion="4.0">

When the Build server builds the solution containing the database project, adding the default targets will also deploy the database. This build will use the Deploy settings selected for ‘My project settings’.

Pros

• Easy to set up

Cons

• Because the default target is changed to Build and Deploy, when a developer do a local Rebuild of the solution, it will also deploy the database (building only won't trigger the deployment)

Answer 2

For anyone still looking for the answer, use an Invoke Process that uses vsdbcmd.exe: http://msdn.microsoft.com/en-us/library/ff805001.aspx

Answer 3

The closest thing I've found so far is this post from Jim Lamb (the Team Foundation PM at Microsoft).

Here he talks vaguely about creating a custom proj file and modifying the default build process template to invoke the proj file.

He also talks about using the upgrade template, which I'd previously ignored. Apparently the upgrade template can be used to invoke a 2008 build definition. I'd rather not invoke and define the entire build based on the legacy proj file though, but at least it is an option.

Neither topic contains sufficient information for me to actually make the modifications necessary, but it does give me a reasonable starting point for some future experimentation.

Answer 4

Here is an example:

http://www.nablasoft.com/alkampfer/index.php/2009/10/06/deploy-a-database-project-with-tfs-build/

It may be that you are simply missing the DeployToDatabase=true