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 (5)

  1. benshire says:

    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?

  2. Barclay Hill says:

    You must set up new variables because the built-ins get special treatment and are populated to how the server is configured. Nothing stops you from creating and using your own. Add new variables for you paths, replace the variables in your SQL files, override these new variables on the command line as I have done in the above sample. Thanks.

  3. JigneshPatel says:

    When I run following command: it fails with the message below it:

    vsdbcmd.exe /a:Deploy /cs:"<coonectionstring>" /manifest:"DB.deploymanifest" /p:DefaultDataPath="D:SQL DB" /p:DefaultLogPath="D:SQL DB"

    Unrecognized command line argument 'DB"'

    Unrecognized command line argument 'DB"'

    This coomand does not support space in the value even it was enclosed with double quotes…

  4. Anonymous says:

    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

  5. Barclay Hill says:

    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.

Skip to main content