building your database solutions outside of Visual Studio

Visual Studio database solutions provide DBAs with a lot of functionality not the least of which is ability to spot errors before scripts are deployed. One thing to be aware, though that there are three levels of error catching –

• as soon as a file is saved, VS checks syntax and references
• at build time references are re-examined and more thorough checking is conducted
• during a deployment execution related errors are surfaced

the first two steps roughly correspond to what happens when a create object script is executed in the Management Studio.

So logical thing to do, of course, would be to re-build you DB solution as you’re making changes. if you have Continuous integration that runs the build process for you that could be one option, but normally it takes time until the build server processes your solution. Especially given that DB solutions tend to take longer than let’s say C# projects. You can of course re-build right in your VS studio, but that means a lot of thumb twiddling or lots of coffee runs.

One simple answer is to use msbuild.exe to re-build your DB “from outside”. I’ve been using a poweshell script to do this (surprise) with a lot of success. It gives me instant visual ques and provides a log file that I can use for further troubleshooting if needed. and also I can use the output for deploying the database to an actual SQL server using vsdbcmd.exe. After having read a great blog post on this subject by jamie thomson aka SSIS Junkie I felt compelled to share my build script. Here it is:

#MSBUILD.PS1
# automate build

[string]$output=""

$logpath="c:\PowerShell\logbuildLog.txt"

if (Test-Path $logpath)
{Remove-Item $logpath}

#output path
$buildOutput="C:\PowerShell\output"

$dbnames=@()


$msbuild="C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\msbuild.exe"

 $cdt=Get-Date

 #save output to a file
 $output=$output+ "script executed at " + $cdt + "`r`n"
 
 #also display the same interactively
 Write-Host "script executed at "  $cdt
 
 $output=$output +"`r`n"
 Write-Host
 
#load project list
 dir . -r  -include *.dbproj |where {$_.Name -notmatch "server"} |select-object fullname,name |
 % {

 $path=$_.fullname
 $dbnames +=$_.Name.replace(".dbproj","")
 $buildOutput=$buildOutput+""+$_.Name.replace(".dbproj","")

#for each dbproj file display its name
 $output=$output+ "********** building " + $_.fullname + " ********************" +"`r`n"
 write-host "********** building "  $_.fullname  " ********************" 
 $output=$output+"`r`n"
 Write-Host

#execute the build and save output
 [string]$output1= &$msbuild $path /nologo /verbosity:n /p:OutputPath=$buildOutput

 $output=$output+$output1 +"`r`n"
 $output=$output+"`r`n"
 $output=$output+ "********** Finished building " + $_.Name + " ********************" +"`r`n"
 
 #check if the build succeeded
 if ($output1.contains("Build succeeded"))
 {
 Write-Host "Build Succeeded"

#display any warnings
$output1 | select-string -pattern "d+?sWarning(s).+$" |%{$_.Matches[0].Value}
 }
 
 #maybe it failed?
 if ($output1.contains("Build FAILED"))
 {
 Write-Host "Build Failed" -foregroundcolor red
 
 #display build warnings
 $output1 | select-string -pattern "d+?sWarning(s).+$" |%{$_.Matches[0].Value}
 }
 Write-Host
 Write-Host "********** Finished building "  $_.Name  " ********************"
 $output=$output+"`r`n"
 $output1=""
 $buildOutput="C:PowerShelloutput"
}

 # save output
 Out-File -filepath $logpath -InputObject $output