Deploy VS 2010 database project using MSBuild and TeamCity

12
votes

Is it possible to deploy a VS 2010 database project using TeamCity?

I am building my whole solution, and deploying a website to my server, this all works fine.

The final step I want to trigger is the deploy of the database project which generates a sql script and deploys it.

I have the "Create a deployment script (.sql) and deploy to the database" option selected as a deploy action, my Configuration target is set to build and deploy the database project, but I just can't figure how to get TeamCity and MSBuild to trigger it.

2
visual-studio-2010visual-studiomsbuildteamcitydatabase-project

2 Answers

16
votes

Visual Studio must be installed for this to work.

For the original SQL Server 2005/2008 Database Project types:

  1. Create a build step of runner type Visual Studio to build the solution.
  2. Create a build step of runner type Command Line. Set Command Executable to C:\Program Files\Microsoft Visual Studio 10.0\VSTSDB\Deploy\VSDBCMD.exe. Set Command Parameters to /a:Deploy /dd:+ /manifest:%system.teamcity.build.checkoutDir%\<PROJECT PATH>\sql\debug\<PROJECT NAME>.deploymanifest. (See here for VSDBCMD.exe parameters).

For the SQL Server Database Project provided by SQL Server Data Tools or Visual Studio 2012/2013:

  1. Create a build step of runner type Visual Studio to build the solution.
  2. Create a build step of runner type Command Line. Set Command Executable to C:\Program Files\Microsoft Visual Studio 10.0\Microsoft SQL Server Data Tools\sqlpackage.exe. See here for sqlpackage.exe parameters. Here's an example with the deployment settings stored in an XML file (created via the Publish option): /Action:Publish /SourceFile:%system.teamcity.build.checkoutDir%\<PROJECT PATH>\bin\Debug\<PROJECT NAME>.dacpac /Profile:%system.teamcity.build.checkoutDir%\<PATH TO PROJECT>\PublishSettings.xml.
10
votes

Since my question got upvoted today, I'm revisiting with the answer I found.

I did the following:

  • created a Deploy step in my build
  • pointed it at my VS database project (.dbroj file)
  • enetered command line params as follows: /t:Deploy /p:TargetDatabase=MyDBName /P:Configuration=%env.Configuration%

Edit 16/11/12: Another upvote, so revisiting again. Since I posted this, things became a bit clearer. I was originally running TeamCity on my dev PC.

When I came to move it onto a server, the only way I could get the database deploy to work was to install Visual Studio on the server which is a pain.