SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Reliably Drop A Database In T-SQL Script


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


   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


   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


  14:     DROP DATABASE [Blah]; 

  15: END; 

  16: GO


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.


Leave a comment on the original post [dbaphilosophy.wordpress.com, opens in a new window]

Loading comments...