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"%(TestData.Identity)"$(BCPOptions)"/>
</Target>
</Project>
Technorati Tags: MSBuild
