Reputation: 3752
Anyone got examples of how to do a DB deployment without TFS in MSBuild. I had automated db deployments in nant at an old position, but need to do it in msbuild at a new job. I was using the nant and setting a boolean flag to trigger processing sql files, but im unsure of how to do this in msbuild. Everything surprising with MSBuild points to TFS
I was using the following algorithm
// Set run updates = false
// Store DB Version from Version Table
// For each file in SQL directory
// if file == db version
// set run updates = true
// else if run updates
// run sql in file
// update db version
I'm open to changes in how this is handled... but can't forsee my company moving to TFS
Upvotes: 1
Views: 1466
Reputation: 3752
I decided to use DBDeploy to do the database deployments. The following works in my situation.
<Target Name="DeployDB">
<RemoveDir Directories="$(MSBuildProjectDirectory)\..\temp" ContinueOnError="true" />
<MakeDir Directories="$(MSBuildProjectDirectory)\..\temp" ContinueOnError="true" />
<Exec Command="$(MSBuildProjectDirectory)\..\lib\dbdeploy\example\tools\nant\bin\nant -buildfile:dbdeploy.build -D:DBConnstring="$(MainDatabaseConnectionString)" -D:SQLDirectory=..\DB -D:OutputFile=..\Temp\Deploy.sql" />
<MSBuild.ExtensionPack.SqlServer.SqlExecute TaskAction="Execute" Files="..\Temp\Deploy.sql" ConnectionString="$(MainDatabaseConnectionString)" />
<RemoveDir Directories="$(MSBuildProjectDirectory)\..\temp" ContinueOnError="true" />
</Target>
with a build file of
<?xml version="1.0" encoding="UTF-8"?>
<project name="dbdeploy_example" default="generate-script" basedir="." xmlns="http://nant.sf.net/release/0.85/nant.xsd">
<loadtasks assembly="../lib/DbDeploy/bin/Net.Sf.Dbdeploy.dll" />
<property name="DBConnstring" value="Server=localhost;Initial Catalog=XXXX;Integrated Security=SSPI;" />
<property name="SQLDirectory" value="." />
<property name="OutputFile" value="deploy.sql" />
<property name="UndoOutput" value="deploy-undo.sql" />
<target name="generate-script" description="generate a sql upgrade script">
<echo message="DBConstring: ${DBConnstring}" />
<echo message="SQLDirectory: ${SQLDirectory}" />
<echo message="OutputFile: ${OutputFile}" />
<echo message="UndoOutput: ${UndoOutput}" />
<dbdeploy dbType="mssql"
dbConnection="${DBConnstring}"
dir="${SQLDirectory}"
outputFile="${OutputFile}"
undoOutputFile="${UndoOutput}" />
</target>
</project>
Upvotes: 3