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
