Thoughts, Tips and Tricks on what I'm currently do for a living. Currently most of my spare time is spent on contributing to Akka.NET.

Thursday, December 18, 2008

How to deploy a VS Database Project GDR using vsdbcmd

With the release of Visual Studio Team System 2008 Database Edition GDR we now have the possibility to use a standalone command for deploying a database project. Unfortunately the documentation for the new vsdbcmd is so full of errors that you cannot use it, see for example “Command-line Reference for VSDBCMD (Deployment and Schema Import)” and “How to: Prepare a Database for Deployment From a Command Prompt by Using VSDBCMD ”. Seems to be written for a previous version.

Properties

The documentation states that you should specify properties using this syntax:

/p:PropertyName:PropertyValue    INCORRECT!

The correct syntax is:

/p:PropertyName=PropertyValue 

Verbose and quiet

It also states that there exists a /verbose or /v option. This option do not exist. It has been removed. It’s verbose by default and you can use the undocumented /quiet or /q to turn of verbosity [source].

Invalid property names

The common deployment properties list isn’t right either. For example the TargetDatabaseName is in fact called TargetDatabase. Have a look in the .sqldeployment and .deploymanifest files for proper naming. These files exists in the directory created when building the project, for example MyDbProject/sql/debug/.

Deploy-example

Below is an example of how to deploy a database project that has been built by Visual Studio or Team Server Foundation (TFS).

Start a command prompt and change directory to the directory that was created when the project was built (for example MyDbProject/sql/debug/) and execute the command below (on a single line).

"%ProgramFiles%\Microsoft Visual Studio 9.0\VSTSDB\deploy\vsdbcmd"
/a:Deploy
/ConnectionString:"Data Source=MyServer;Integrated Security=True;"
/dsp:SQL
/manifest:MyDbProject.deploymanifest
/p:TargetDatabase=MyDb
/dd

This will create a .sql file and deploy it, i.e. execute it on the server. If you remove the /dd option the .sql will be created but not deployed.