Deploying your Database Project without VSTSDB installed

The  release of Visual Studio Team System Database Edition GDR version includes a very powerful tool to import and deploy database schemas from boxes where Visual Studio is not installed.  The tool is a command line utility capable of:

  • Importing a database schema from a live database into a dbschema file;

  • Deploying a database schema file to a live database (full and incremental deployments);

  • Generating deployment scripts based on a dbschema file and a live database (full and incremental deployments).

The utility is called VSDBCMD.exe and is installed to the default location of %\ProgramFiles%\Microsoft Visual Studio 9.0\VSTSDB\Deploy when you install the GDR release.

VSDBCMD comes in real handy when you need to automate your database deployments.  You can use it to deploy database changes to environments you can not connect to from your desktop by running the utility locally from a flash drive or installer.  You can also run the utility while on a customer site to import a database schema into a dbschema file that you can later use to compare against your database project or reference its schema.  There are numerous scenarios where the utility can simplify working with remote databases.

To use the utility on a box without VSTSDB installed to import or generate script you need the following:

To use the utility on a box without VSTSDB installed to deploy you also need the SQL Batch Parser which is installed with:

Here is an example of using VSDBCMD to import the Northwind Database into a dbschema file; then deploy the dbschema file as a new database back to the same database instance; and finally generating a deployment script based on the imported dbschema file.

ECHO “Importing Database Schema…”
vsdbcmd.exe /a:Import /cs:”Server=MYSQLSERVER\SQL2k8;Integrated Security=true;Pooling=false;Initial Catalog=Northwind;” /dsp:Sql  /model:”NorthwindVSDBCMD.dbschema”
ECHO “Deploying Database Schema…”
vsdbcmd.exe /a:Deploy /cs:”Server=MYSQLSERVER\SQL2k8;Integrated Security=true;Pooling=false” /dsp:Sql /dd+ /model:”NorthwindVSDBCMD.dbschema”  /p:TargetDatabase=”NewNorthWind”
ECHO “Generating Database Deployment Script…”
vsdbcmd.exe /a:Deploy /cs:”Server=MYSQLSERVER\SQL2k8;Integrated Security=true;Pooling=false” /dsp:Sql /script:CreateNorthWindDB.sql /model:”NorthwindVSDBCMD.dbschema” /p:TargetDatabase=”NorthWindFromScript”

To see the list of available options based on the DSP and Action you can execute:

vsdbcmd.exe /dsp:Sql  /?
vsdbcmd.exe /a:Import /? /dsp:Sql /cs:”MYSQLSERVER\SQL2k8;Integrated Security=true;Pooling=false;Initial Catalog=Northwind;”
vsdbcmd.exe /a:Deploy /? /dsp:Sql /cs:”MYSQLSERVER\SQL2k8;Integrated Security=true;Pooling=false;Initial Catalog=Northwind;”

Comments (4)

  1. Bob mihada says:

    Thank you!!! I took this one step further and created a batchscript here: I can only hope that helps someone as this post helped me.

  2. deadlydog says:

    I had lots of trouble with getting VSDMCMD to actually run the script it generated on a specific database.  In the end just using MSBuild was so much easier.  Just point it at the .dbproj file and tell it what database to update.…/deploy-a-database-project-with-tfs-build-2010

  3. bahill says:

    VSDBCMD can create a deployed script and execute a script after creating it from the build output of a database project, but it can not execute a script passed to it.  You need SQLCMD for that.  You have VSDBCMD deploy the script it produces by passing /dd:+.