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
