Automatically deploying data dude projects with vsdbcmd.exe

In a previous post, I talked about automatically building a database project outside of Visual Studio. Just for the sake of a feeling of completeness, I will also add a script for deploying the said project to a database of your choice.

There are many ways to deploy a database project. See for example this msdn reference for examples of deploying without having to specify a connection string on a command line, or Justin’s blog on using TFS build templates, or jamie thomson on using msbuild scripts.  For my part, I’m driven by a desire to accomplish as much as possible with doing as little as possible. So, after I run the build script and all is well, I want to re-use the build output files wi
thout modifying them at all.

Build the output directory should contain the following files:

  • dbname.dbschema
  • dbname.deploymanifest
  • dbname.sqlcmdvars
  • dbname.sqldeployment
  • dbname.sqlsettings
  • dbname.PostDeployment.sql
  • dbname.PreDeployment.sql

they should contain all the info that we need to run vsdbcmd.exe. we’ll just need to add few params:

  • connections string
  • manifest location
  • target db name

that’s all really. We’ll need to add some basic params just so that vsdbcmd.exe knows what we want from it, such as:

  • action (deploy)
  • deploy to database
  • verbose/silent mode

As you may have guessed, I’m going to use powershell to run it:

 $connStr="Persist Security Info=True;Initial Catalog=""dbname"";Data Source=127.0.0.1;uid=""user"";pwd=""password"";" 
$pr1="/Action:Deploy";
$pr2="/dd:+"; 
$pr3="/cs:"+$connStr 
$pr4="/manifest:"""+$buildOutput+""+$dbname+""+$dbname+".deploymanifest"" " 
$pr5="/p:TargetDatabase="""+$dbname+""" " 
$pr6="/q:+"  

Check out this msdn page for more info on individual parameters. $buildOutput  is the path to the build output files. If I want to deploy a bunch of databases, I could turn $buildOutput  and $dbname into parameters and loop through the project directories. But  I don’t find myself deploying many databases at once. If an environment really needs to be refreshed, it’s better to do it from a CI tool, so that there is a record of action and so on.

You may have noticed that I put each param into a separate variable. This is because of a strange quirk of Powershell – vsdbcmd.exe combination. You can’t just create one long string with parameters and execute vsdbcmd with it. Vsdbcmd will think that the entire string is an /Action command. From a cmd batch file it works fine.

In any case, all’s left is to specify a path to vsdbcmd and execute it

$ex="C:\Program Files\Microsoft Visual Studio 10.0\VSTSDB\Deploy\vsdbcmd.exe"
#execute the deploy command
& $ex $pr1 $pr2 $pr3 $pr4 $pr5 $pr6; 

That’s it really. As simple as it gets. If something went wrong vsdbcmd will complain about it. If you want to be a bit more proactive about it, you can follow Gert D’s   suggestion with a Powershell twist:

#check if succeeded or not
if ($?) {Write-Host "database ",$dbname," successfully deployed"}
	else {Write-Host "deployment for ",$dbname," failed"} 

Download a sample powershell script.

Comments

comments

This entry was posted in Powershell, SQL server, Visual Studio and tagged , , . Bookmark the permalink.

One Response to Automatically deploying data dude projects with vsdbcmd.exe

  1. ibs diet says:

    This really answered my problem, thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>