New 2010 VSDBCMD Features - Target Model and Override SQLCMD Variables

  • Article

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.