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
