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.
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.