So I’m taking a break from writing about other Agile stuff for a post. :) I’m still going
to get back
to the other subjects, but this is fun too. Something I’ve done quite a bit of is MSBuild and CI work. I’m experimenting with ways
to improve what I’ve done in the past, particularly around database CI. Today, I developed a mechanism for starting from scratch with your database. By scratch, I mean blowing away the existing database and creating it again from a single command line call. I’m a firm believer that developers should be able
to get
to a known clean state at the database level with a single command and that they should be operating off of their own isolated database
to improve productivity. These scripts will help that. Here’s how I did it. First, we have
to disconnect users. I did so using the help of a script from sql server central. Note that I’m using sqlcmd variable replacement. -- kills all the users in a particular database
-- dlhatheway/3M, 11-Jun-2000
declare @arg_dbname sysname
declare @a_spid smallint
declare @msg varchar(255)
declare @a_dbid int
set @arg_dbname = '$(DatabaseName)'
select
@a_dbid = sdb.dbid
from master..sysdatabases sdb
where sdb.name = @arg_dbname
declare db_users insensitive cursor for
select
sp.spid
from master..sysprocesses sp
where sp.dbid = @a_dbid
open db_users
fetch next from db_users into @a_spid
while @@fetch_status = 0
begin
select @msg = 'kill '+convert(char(5),@a_spid)
print @msg
execute (@msg)
fetch next from db_users into @a_spid
end
close db_users
deallocate db_users
GO
Once all users are booted from the database, we can commence with recreating the database. I generated the script that is used
to create a database from SQL Server management studio, so I’m only going
to show the bits that weren’t generated that are important. There are a bunch of Alter Database statements that aren’t shown.
First, I had
to find the default location of the database files in the install, since they can be in many different locations. I used Method 1 from a technet blog and then modified it a bit
to do what I needed
to do. I ended up using dynamic SQL because for the life of me, I couldn’t get the “Filename” property
to not return an error when I used anything besides a string. I’m dropping the database first, if it exists. Here’s the code:
IF EXISTS(SELECT 1 FROM [master].[sys].[databases] WHERE [name] = N'$(DatabaseName)')
BEGIN
drop database $(DatabaseName)
END;
go
IF EXISTS(SELECT 1 FROM [master].[sys].[databases] WHERE [name] = 'zzTempDBForDefaultPath')
BEGIN
DROP DATABASE zzTempDBForDefaultPath
END;
-- Create temp database. Because
no options are given, the default data and --- log path locations are used
CREATE DATABASE zzTempDBForDefaultPath;
DECLARE @Default_Data_Path VARCHAR(512),
@Default_Log_Path VARCHAR(512);
--Get the default data path
SELECT @Default_Data_Path =
( SELECT LEFT(physical_name,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1)
FROM sys.master_files mf
INNER JOIN sys.[databases] d
ON mf.[database_id] = d.[database_id]
WHERE d.[name] = 'zzTempDBForDefaultPath' AND type = 0);
--Get the default Log path
SELECT @Default_Log_Path =
( SELECT LEFT(physical_name,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1)
FROM sys.master_files mf
INNER JOIN sys.[databases] d
ON mf.[database_id] = d.[database_id]
WHERE d.[name] = 'zzTempDBForDefaultPath' AND type = 1);
--Clean up.
IF EXISTS(SELECT 1 FROM [master].[sys].[databases] WHERE [name] = 'zzTempDBForDefaultPath')
BEGIN
DROP DATABASE zzTempDBForDefaultPath
END;
DECLARE @SQL nvarchar(max)
SET @SQL=
'CREATE DATABASE $(DatabaseName) ON PRIMARY
( NAME = N''$(DatabaseName)'', FILENAME = N''' + @Default_Data_Path + N'$(DatabaseName)' + '.mdf' + ''', SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N''$(DatabaseName)Log'', FILENAME = N''' + @Default_Log_Path + N'$(DatabaseName)' + '.ldf' + ''', SIZE = 1024KB , FILEGROWTH = 10%)
'
exec (@SQL)
GO
And with that, your database is created. You can run these scripts on any server and on any database name.
To do that, I created an MSBuild script that looks like this:
<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003"
ToolsVersion="4.0">
<PropertyGroup>
<DatabaseName>MyDatabase</DatabaseName>
<Server>localhost</Server>
<SqlCmd>sqlcmd -v DatabaseName=$(DatabaseName) -S $(Server) -i </SqlCmd>
<ScriptDirectory>.\Scripts</ScriptDirectory>
</PropertyGroup>
<Target Name ="Rebuild">
<ItemGroup>
<ScriptFiles Include="$(ScriptDirectory)\*.sql"/>
</ItemGroup>
<Exec Command="$(SqlCmd) "%(ScriptFiles.Identity)"" ContinueOnError="false"/>
</Target>
</Project>
Note that the Scripts directory is underneath the directory where I’m running the msbuild command and is relative
to that directory. Note also that the target is using batching
to run each script in the scripts subdirectory, one after the other. Each script is passed
to the sqlcmd command line execution using the .Identity property on the itemgroup that is created. This target file is saved in the file “Database.target”.
To make this work, you’ll need msbuild in your path, and then run the following command:
msbuild database.target /target:Rebuild
Once you’ve got your virgin database setup, you’d then need
to use a tool like dbdeploy.net
to determine that it was a virgin database, build a change script based on the change scripts, and then you’d want another sqlcmd call
to update the database with the appropriate scripts. I’m doing that next, so I’ll post a blog update when I’ve got it working.
Technorati Tags: MSBuild,Agile,CI,Database
