One of the aims of Continuous Integration is to always have a releasable version of a project ready. For the code itself this is no problem but if the project includes a database with schema changes then it can be a little more complicated, especially if you have several versions of an application that are supported. You need a way of creating database change scripts for each version of the database as part of the build process.
Red-Gate make an excellent product called SQL Compare which can compare two database versions and produce a script to convert one version to the other and a SQL Toolkit that allows you to automate the process. They had a link to a NAnt task on their site but I found this a little inflexible so I created the one below which gives more control over what is compared and also allows you to set various SQL Compare options.
The main feature are that you can compare the current development SQL Server database against other databases and / or database snapshots with script generated to convert to and from each version. You can also save a database snapshot which can be put back into source control (as can the change scripts).
Here is an example of the configuration:
<?xml version="1.0"?>
<project name="comparesql" default="comparesql" basedir=".">
<loadtasks assembly="C:\Program Files\nantsql-0.85-rc3\bin\NAnt.SqlTasks.dll" />
<!-- target .NET framework and corresponding compiler directives -->
<property name="nant.settings.currentframework" value="net-1.1"/>
<target name="comparesql">
<sql-compare
folder="c:\output"
title="TRUNK"
server="DEVSERVER"
database="AppDb"
uid="dbuser"
pwd="dbpassword"
snapshotFilename="trunk.snp"
scriptFilename="trunk.sql"
saveScript="true"
saveSnapshot="true">
<objects>
<object type="Field"/>
<object type="Function"/>
<object type="Index"/>
<object type="Rule"/>
<object type="StoredProcedure"/>
<object type="Table"/>
<object type="Trigger"/>
<object type="UserDefinedType"/>
<object type="View"/>
</objects>
<options>
<option type="IncludeDependencies"/>
<option type="IgnoreFullTextIndexing"/>
<option type="IgnoreUserProperties"/>
<option type="IgnoreUsers"/>
<option type="IgnoreOwners"/>
<option type="IgnorePermissions"/>
<option type="TargetIsPreYukon"/>
</options>
<databases>
<database
title="BRANCH100"
server="DEVSERVER"
database="AppDb"
uid="dbuser"
pwd="dbpassword"
snapshotFilename="BRANCH100.snp"
scriptFilename="BRANCH100.sql"
saveSnapshot="true"
saveScript="true"/>
<database
title="BRANCH115"
server="DEVSERVER"
database="AppDb"
uid="dbuser"
pwd="dbpassword"
snapshotFilename="BRANCH115.snp"
scriptFilename="BRANCH115.sql"
saveSnapshot="true"
saveScript="true"/>
</databases>
</sql-compare>
</target>
</project>
I will post the source code on the main InteSoft.NET site for download.