Automate Testing of Database Change Scripts

As part of your build process you can now use the Nant Tasks of SQL Admin Studio to automate a process similar to this.

1. Backup the Test Database
2. Generate the Change Script from the Current Development Database and the Test database
3. Apply the Change Script
4. Run NUnit Tests if you like
5. Restore the Test Database back to it's original state

So in this example I have been working on changes in Northwind2 and now I want to automate the process of creating and testing my updates for the Northwind database.

<?xml version="1.0" ?>
<project name="Test Database Release" default="Test" xmlns="http://simego.com/nant.xsd">
 
  <loadtasks assembly="C:\Program Files\Simego\SQL Tools\Simego.SQLTools.Tasks.dll" />

  <target name="Test">

    <sqlbackupdatabase
        srcServer=".\SQLEXPRESS"
        srcDatabaseName="Northwind"
        backupSetOption="OVERWRITE"
        fileName="C:\Temp\Database.bck"
        backupName="Northwind Backup"
    />

    <sqlscriptdatabasedifference
        srcServer=".\SQLEXPRESS"
        srcDatabaseName="Northwind2"
        srcObjects="*"
        dstServer=".\SQLEXPRESS"
        dstDatabaseName="Northwind"
        fileName="C:\Temp\Database-Differences.sql"
    />

    <sqlexecute
        srcServer=".\SQLEXPRESS"
        srcDatabaseName="Northwind"
        fileName="C:\Temp\Database-Differences.sql"
    />
   
    <sqlrestoredatabase
        srcServer=".\SQLEXPRESS"
        srcDatabaseName="Northwind"
        fileName="C:\Temp\Database.bck"
    />
      
  </target>

</project>