다음을 통해 공유

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 OFF
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"
PAUSE
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"
PAUSE
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"
PAUSE

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

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

Comments

  • Anonymous
    February 20, 2009
    PingBack from http://www.clickandsolve.com/?p=12484
  • Anonymous
    October 19, 2010
    Thank you!!! I took this one step further and created a batchscript here: http://bit.ly/dg19vY. I can only hope that helps someone as this post helped me.
  • Anonymous
    July 15, 2011
    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. www.codewrecks.com/.../deploy-a-database-project-with-tfs-build-2010
  • Anonymous
    July 18, 2011
    The comment has been removed