I promised I’d have a simpler mechanism for rebuilding the database.  Below is a complete MSBuild targets file for rebuilding the database from scratch.  I don’t know if I’ve explained the rational for this.  The reason why you’d WANT to do this is so that each developer has a clean version of the database on their local machine.  This also includes the continuous integration environment.  Basically, you can do whatever you want to the database without fear, and in a minute or two, have a completely rebuilt database structure.

DBDeploy (including the KTSC build task for dbdeploy) is used in this script to do change tracking on the database itself.  The MSBuild ExtensionPack is used in this target file.  You can get an MSBuild DBDeploy task here.

There are two database scripts that you’ll see below.  First is the task for creating an admin (dbo) user in the system.  This script looks like the following:

USE [master]
GO

If not Exists (select Name from sys.sql_logins where name = '$(User)')
BEGIN
    CREATE LOGIN [$(User)] WITH PASSWORD=N'$(Password)', DEFAULT_DATABASE=[$(DatabaseName)], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
END
GO
EXEC master..sp_addsrvrolemember @loginame = N'$(User)', @rolename = N'sysadmin'
GO
USE [$(DatabaseName)]
GO
CREATE USER [$(User)] FOR LOGIN [$(User)]
GO
ALTER USER [$(User)] WITH DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember N'db_owner', N'$(User)'
GO

The second creates the changelog table.  This script can also be found in the dbdeploy.net install\scripts directory.

CREATE TABLE changelog (
  change_number INTEGER NOT NULL,
  delta_set VARCHAR(10) NOT NULL,
  start_dt DATETIME NOT NULL,
  complete_dt DATETIME NULL,
  applied_by VARCHAR(100) NOT NULL,
  description VARCHAR(500) NOT NULL
)
GO

ALTER TABLE changelog ADD CONSTRAINT Pkchangelog PRIMARY KEY (change_number, delta_set)
GO

Finally, Here’s the targets file.

<Projectxmlns="http://schemas.microsoft.com/developer/msbuild/2003" ToolsVersion="4.0" DefaultTargets="Update">

  <PropertyGroup>
    <DatabaseName>TestDatabase</DatabaseName>
    <Server>localhost</Server>
    <ScriptDirectory>.\Scripts</ScriptDirectory>
    <RebuildDirectory>.\Rebuild</RebuildDirectory>
    <TestDataDirectory>.\TestData</TestDataDirectory>
    <DbDeploy>.\DBDeploy</DbDeploy>
    <User>TestUser</User>
    <Password>TestPassword</Password>
    <BCP>bcp</BCP>
    <BCPOptions>-S$(Server) -U$(User) -P$(Password) -N -E -k</BCPOptions>
    <OutputFileName>dbDeploy-output.sql</OutputFileName>
    <UndoFileName>dbDeploy-output-undo.sql</UndoFileName>
    <LastChangeToApply>99999</LastChangeToApply>
  </PropertyGroup>
 
  <ImportProject="$(MSBuildExtensionsPath)\ExtensionPack\4.0\MSBuild.ExtensionPack.tasks"/>
  <UsingTask TaskName="Ktsc.Build.DBDeploy" AssemblyFile="$(DbDeploy)\Ktsc.Build.dll"/>

  <ItemGroup>
    <VariableInclude="DatabaseName">
      <Value>$(DatabaseName)</Value>
    </Variable>
    <VariableInclude="Server">
      <Value>$(Server)</Value>
    </Variable>
    <VariableInclude="User">
      <Value>$(User)</Value>
    </Variable>
    <VariableInclude="Password">
      <Value>$(Password)</Value>
    </Variable>
  </ItemGroup>
 
  <TargetName="Rebuild">
    <!--Take the database offline to disconnect any users. Requires that the current user is an admin of the sql server machine.-->
    <MSBuild.ExtensionPack.SqlServer.SqlCmd Variables="@(Variable)" Database="$(DatabaseName)" TaskAction="Execute" CommandLineQuery ="ALTER DATABASE $(DatabaseName) SET OFFLINE WITH ROLLBACK IMMEDIATE"/>
   
    <!--Bring it back online.  If you don't, the database files won't be deleted.-->
    <MSBuild.ExtensionPack.Sql2008.DatabaseTaskAction="SetOnline" DatabaseItem="$(DatabaseName)"/>

    <!--Delete the database, removing the existing files.-->
    <MSBuild.ExtensionPack.Sql2008.DatabaseTaskAction="Delete" DatabaseItem="$(DatabaseName)"/>

    <!--Create the new database in the default database path location.-->
    <MSBuild.ExtensionPack.Sql2008.DatabaseTaskAction="Create" DatabaseItem="$(DatabaseName)" Force="True"/>
   
    <!--Create admin user-->
    <MSBuild.ExtensionPack.SqlServer.SqlCmd TaskAction="Execute" Server="(local)" Database="$(DatabaseName)" InputFiles="$(RebuildDirectory)\0002 Create Admin User.sql" Variables="@(Variable)" />

    <!--Create the dbdeploy changelog.-->
    <MSBuild.ExtensionPack.SqlServer.SqlCmd TaskAction="Execute" Server="(local)" Database="$(DatabaseName)" LogOn="$(User)" Password="$(Password)" InputFiles="$(RebuildDirectory)\0003 Create Changelog.sql" Variables="@(Variable)" />

    <CallTarget Targets="Update;ImportData"/>
    </Target> 

  <TargetName="Update" DependsOnTargets="CreateUpdateScript">
    <MSBuild.ExtensionPack.SqlServer.SqlCmd TaskAction="Execute" Server="(local)" Database="$(DatabaseName)" LogOn="$(User)" Password="$(Password)" InputFiles="$(OutputFileName)" Variables="@(Variable)" />
  </Target>

  <TargetName="CreateUpdateScript">
    <ktsc.Build.DBDeploy DbType="mssql"
                                       DbConnection="User=$(User);Password=$(Password);Data Source=$(Server);Initial Catalog=$(DatabaseName);"
                                       Dir="$(ScriptDirectory)"
                                       OutputFile="..\$(OutputFileName)"
                                       UndoOutputFile="..\$(UndoFileName)"
                                       LastChangeToApply="$(LastChangeToApply)"/>
  </Target>
 
  <TargetName="ImportData">
    <ItemGroup>
      <TestData Include="$(TestDataDirectory)\*.dat"/>
    </ItemGroup>

    <ExecCommand="$(BCP) $(DatabaseName).dbo.%(TestData.Filename) in&quot;%(TestData.Identity)&quot;$(BCPOptions)"/>
  </Target>
</Project>