New 2010 VSDBCMD Features - Target Model and Override SQLCMD Variables

  • [アーティクル]

Targeting a dbschema model file for deployment 

Since releasing GDR we have had customer's ask for the ability to target a dbschema file through vsdbcmd. We were able to add this for the 2010 release. The primary use of this is to diff 2 dbschema files and generate an update script for review. This simplifies the process of doing this through Schema Compare and can easily be automated. It is especially helpful when coupled with the deployment plan analyzer extension. I highly recommend going through the walkthrough to build this extension.

If you are a vsdbcmd user then you will want to check this out. Essentially you call vsdbcmd like you have in the past, but now you can specify a target dbschema file like so:

C:\Work\Buildoutputs>vsdbcmd /action:deploy /dd:- /dsp:sql

/modelfile:newbuild.dbschema

/targetmodelfile:lastbuild.dbschema

/p:TargetDatabase="DiffTest"

/Quiet:- /DeploymentScriptFile:diff.sql

>>TSD00566 Deployment script generated to:
>>C:\Work\Buildoutputs\diff.sql

Only a script is generated. The target dbschema is not updated. This capability also allows you to automate the generation of differential script between 2 databases if you also do the imports first. See my previous post regarding how to import. 

Overriding SQLCMD variables at deployment  

Also frequently asked for is a way to override SQLCMD variables at deployment. Overriding SQLCMD variables at deployment time is now a snap. No more needing to swap out files if you need to override a variable for a specific target. If you have a value that you want substituted, like a build number for example, you can now do this at deploy time. You do this by simply supplying the override like so:

C:\Work\MyAppsDB\sql\debug>vsdbcmd /action:deploy /dd:+

      /dsp:sql /modelfile:MyAppsDB.dbschema

      /cs:"Server=.\SQLEXPRESS;Integrated Security=true;Pooling=false"

      /p:TargetDatabase="LatestDBBuild"

      /Quiet:-

      /DeploymentScriptFile:LatestDBBuild.sql

      /p:SqlCommandVariablesFile="DeployTest_database.sqlcmdvars"

      /p:BuildVersion="100317.09"

Lastly, you no longer need to add the regkey the GDR version of vsdbcmd depended on. The other prereqs still apply though.

You now have access to vsdbcmd when installing Visual Studio 2010 Professional, Premium, Ultimate or 2010 Team Foundation Server. A Visual Studio Command Prompt is the easiest way to start using vsdbcmd as the paths are already there for you.

You can get the RC of 2010 from here.

Comments

  • Anonymous
    May 07, 2010
    I have gone around and around on this trying to figure out how to override DefaultDataPath and DefaultLogPath with no luck.  I've tried passing as /p:DefaultDataPath="{PATH}", I've tried forcing the values in the cmdvars file but nothing works.  Is there a trick to getting the DefaultDataPath and DefaultLogPath to used by VSDBCMD?
  • Anonymous
    May 10, 2010
    The comment has been removed
  • Anonymous
    February 17, 2011
    The comment has been removed
  • Anonymous
    August 02, 2012
    Hi,Can we target a database instead of .dbschema file to generate diff script? Usually we have a copy of production database restored so that I can compare changes and generate deployment script.Thanks,Bala
  • Anonymous
    August 09, 2012
    Hi Bala, yes you can target a database through the connection string parameter.  See the vsdbcmd reference linked at the top of the blog post.