Bulletproof way to DROP and CREATE a database under Continuous Integration.

Question

I am attempting to drop and recreate a database from my CI setup. But I'm finding it difficult to automate the dropping and creation of the database, which is to be expected given the complexities of the db being in use. Sometimes the process hangs, errors out with "db is currently in use" or just takes too long. I don't care if the db is in use, I want to kill it and create it again. Does some one have a straight shot method to do this? alternatively does anyone have experience dropping all objects in the db instead of dropping the db itself?

USE master

--Create a database
IF EXISTS(SELECT name FROM sys.databases
    WHERE name = 'mydb')
BEGIN
 ALTER DATABASE mydb
 SET SINGLE_USER --or RESTRICTED_USER
 --WITH ROLLBACK IMMEDIATE
    DROP DATABASE uAbraham_MapSifterAuthority
END

CREATE DATABASE mydb;

Answer 1

We use Hudson to rebuild staging sites for our QA team all the time. We kill connections, drop the database, then restore/rebuild/remigrate a DB.

This is what I use to kill connections so I can drop a DB.

USE MASTER
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_KillDatabaseProcesses]') AND type in (N'P', N'PC'))
   DROP PROCEDURE [dbo].[sp_KillDatabaseProcesses]
GO
CREATE PROCEDURE dbo.sp_KillDatabaseProcesses(@databaseName varchar(100))     
   AS
   DECLARE @databaseId int,
           @sysProcessId int,
           @cmd varchar(1000)
   EXEC ('USE MASTER')
   SELECT @databaseId = dbid FROM master..sysdatabases
      WHERE [name] = @databaseName
   DECLARE sysProcessIdCursor CURSOR FOR
      SELECT spid FROM [master]..[sysprocesses] WHERE [dbid] = @databaseId

   OPEN sysProcessIdCursor
   FETCH NEXT FROM sysProcessIdCursor INTO @sysProcessId WHILE @@fetch_status = 0
   BEGIN
      SET @cmd = 'KILL '+ convert(nvarchar(30),@sysProcessId)
      PRINT @cmd
      EXEC(@cmd)
      FETCH NEXT FROM sysProcessIdCursor INTO @sysProcessId
   END
   DEALLOCATE sysProcessIdCursor
GO

Answer 2

Setting single user with rollback immediate is the typical way of kicking everyone out before a drop.

But I'm a bit surprised that your CI drops the DB and creates it in the same script. Normally the CI should deploy the database, using the deploy script/methods of your build deliverable, just as a customer would do it. But you can't seriously have a deployment script that drops the database on the customer deployment. Usually the CI has a step to cleanup/flatten the test server, and then run the build deliverable setup process, which will deploy the database. And you should also have a story for upgrade scenarios, perhaps using an application metadata version upgrade step.