I read with interests about Greg Low’s post “Reliably Dropping a Database in a T-SQL Script is Too Hard”, I think I have a solution, which at least I cannot prove it not working so far.

   1: USE master; 

   2: GO

   3:  

   4: IF EXISTS(SELECT 1 FROM sys.databases WHERE name = N'Blah')

   5: BEGIN 

   6:     use [Blah]

   7:     ALTER DATABASE Blah SET SINGLE_USER WITH ROLLBACK IMMEDIATE; -- all OTHER sessions inside [Blah] will be closed

   8:  

   9:     alter database [Blah] set recovery full;

  10:     backup database [Blah] to disk='NUL' with init; 

  11:     use master;

  12:     backup log [Blah] to disk='NUL' with norecovery; -- [Blah] db will be in restoring mode

  13:  

  14:     DROP DATABASE [Blah]; 

  15: END; 

  16: GO

  17:  

There are two points worth mentioning here:

1. Before setting the database [Blah] to single_user mode, we need to be inside [Blah] (line 6), this way, all other sessions will be killed by line 7

2. Line 12 backup log with norecovery will set the database [Blah] to restoring mode and so no other sessions can access it.

Note, this code is for illustration and easy-explanation only, in real world, line 6 to 14 (i.e. the IF block) should be inside a dynamic sql string, otherwise, if you do not have [Blah] database in the first place, you will get the error.