data dude

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 using TFS build templates, or 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 without 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=;uid=""user"";pwd=""password"";" 
$pr4="/manifest:"""+$buildOutput+""+$dbname+""+$dbname+".deploymanifest"" " 
$pr5="/p:TargetDatabase="""+$dbname+""" " 

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"} 


Share this post

Share on facebook
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Reply

Your email address will not be published.

Are you #CuriousAboutData? This is a members’ only discussion forum for any CTO, Developer, DBA, Data Engineer/Scientist — or anyone who just wants to know more about using and managing data. Members enjoy open debate, ask/ answer questions, offer opinions, expertise, and support. If your curiosity has no limits, this group is for you!