I recently read a blog post titled Automating Database Script Execution by Thomas Weller.  I agree with many of his points concerning databases.  Most applications today are basically useless without a database.  That said, there is nothing worse than working with a project where the database is a nightmare to deal with.  You spend a lot of wasted time just trying to get to the point where can actually start developing and doing actual work.

As a developer you absolutely must be able to install a local version of the database with relative ease.  If you cannot do that you are dead in the water.

With that said, I always have two simple rules for projects that I work on:

1) Database creation scripts must be kept under source control

2) You must be able to install the database with a single click (OK, maybe a double click)

The need for the database to be installed with a single click is vital – especially for new project development where the data model is changing frequently and closely mimics the domain model.  It is also crucial for automated integration tests.  Continuous build tools must be able to install a fresh version of the database before kicking off the integration tests.

The script order for installing the database should always be as follows:

1) Create tables

2) Create foreign keys

3) Create integrity constraints

4) Create functions, views, and stored procedures

5) Insert static data (for lookup tables, etc)

6) Insert test data

The above order is very important.  I was recently on a project where the static data was installed before the foreign keys were created.  When I asked the owner of such logic why he was doing it that way he said, “Because that way I know that the data won’t violate the foreign keys.”   Sure enough, when I switched the order of the scripts I was bombarded with a plethora of foreign key violations.  Stuff like that makes me want to vomit. 

The best part of all is that it is easy to do.  My preferred approach uses MSBuild.   The script for performing the database install is simply:

<Project ToolsVersion="3.5" DefaultTargets="InstallDatabases" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">

	<UsingTask AssemblyFile="$(CommonLib)\Build.Tasks.dll" TaskName="Build.Tasks.DatabaseBulkInstall" />
	
	<Target Name="InstallDatabases">
		
		<DatabaseBulkInstall 		
			Directory="$(MSBuildProjectDirectory)" 
			ConnectionStringFilePath="$(ConnectionStringFilePath)" />
	
	</Target>

</Project>

This script can then be installed via a .bat file that contains the following command line:

"%windir%\Microsoft.NET\Framework\v3.5\MSBuild.exe" Install.proj /target:InstallDatabases
pause

The “DatabaseBulkInstall” tasks is really quite straightforward as well.  It iterates through the current directory and assumes that any immediate child directories contain database installation scripts (.sql files) and that the name of the directory is the name of the database.  This actually allows us to install many databases with a single click.  The task itself simply uses SqlCommands to execute the scripts.

One important note is that it completely (and forcefully) drops and recreates the database before running the scripts.  The code for doing that is:

StringBuilder sql = new StringBuilder();

sql.AppendLine("IF EXISTS(SELECT * FROM sysdatabases WHERE name='{0}') EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = '{0}'");
sql.AppendLine("USE [master]");
sql.AppendLine("IF EXISTS(SELECT * FROM sysdatabases WHERE name='{0}') ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
sql.AppendLine("IF EXISTS(SELECT * FROM sysdatabases WHERE name='{0}') ALTER DATABASE [{0}] SET SINGLE_USER ");
sql.AppendLine("USE [master]");
sql.AppendLine("IF EXISTS(SELECT * FROM sysdatabases WHERE name='{0}') DROP DATABASE [{0}]");

sql.AppendLine("CREATE DATABASE [{0}]");

Execute(connection, string.Format(sql.ToString(), DatabaseName));

The fact that the above script forcefully drops the database allows us to avoid having to deal with any linger database connections that would normally prevent us from dropping the database.  After all, we are only allowed one click.