Recently I joined a project that was using a Sql Server Database Project to manage their DB scheme and so far it is pretty cool.  This project type like others has a deploy/publish option which will allow you to publish your changes to a target database.  And like other project types this is very easy to do via Visual Studio, but if you know me you will know that I like to automate tasks like these rather than have to use the IDE so I did.  Here is what I did in order to use pSake, a build automation tool written in powershell, to handle my clean, build and deployment of my database projects.

Step 1: Learn

Understand the automation options available for this project type and MSBuild

Step 2: Create your psake task file (database.upgrade.ps1)

Framework "4.0x64"

properties {
    $buildSolution = "Database.sqlproj"
    $targetServer = $parmTargetServer
    $targetDatabase = $parmTargetdatabase
    $databaseUsername = $parmDatabaseUsername
    $databasePassword = $parmDatabasePassword
}


task default -depends upgrade

task upgrade{

    Write-Host "Upgrading the $target_database database from $build_solution" 
        
    msbuild /target:clean`;build`;deploy 
                /p:UseSandboxSettings=false 
                /p:TargetDatabase=$targetDatabase 
                /p:TargetConnectionString=`"Data Source=$targetServer`;user id=$databaseUsername`;password=$databasePassword`;Pooling=False`" $buildSolution
    
}

**** Please not that the formatting of the MSBuild above will NOT RUN and is done to allow the sample code to be clean.  You the entire MSBuild task needs to be on ONE LINE ****

**** I am setting my properties to ‘parm’ variables because I am passing these into the script by using the –parameters feature of psake ****

There is one part of the MSBuild task above that you MUST PAY ATTENTION TO, that is the way items are escaped.  They are being escaped with the back tick (`).  If you omit this you are going to get some odd errors depending on what you do.  These errors could range from ‘Multiple projects were provided’ to issues with the connection string not being formatted.  The root cause for these errors and the need to use the back tick (`) has to do with white space in the connection string… ugg

One you have this psake task setup you can call it like any other task and it should just work.

Till next time,