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