Drop SQL Server databases in Recovery Mode

Hi Guys,

Today met with an unusual situation where a couple of drives in the Dev box crashed. Luckily the system databases were not affected. So cooked up a script to generate the restore of the user databases from the last good backup. Should be a piece of cake I assumed with the restore script to my rescue. 

Sample restore Script. 

--------------------------------------------------------------------------------

DECLARE @dbname sysname ='dba'

SELECT 'RESTORE database [' + @dbname + '] from disk =' AS 'restore script'

UNION ALL 

SELECT  'WITH ' AS 'restore script'

UNION all

SELECT 

'move '''+name+''' ' + 'to ''' +physical_name+''',' AS 'restore script'

FROM sys.master_files

WHERE 1=1

AND database_id=DB_ID(@dbname)

UNION all

SELECT 'stats=1,replace ' AS 'restore script'

-----------------------------------------------------------------------------------------

Well those of you thinking this was a happily ever after story you are almost wrong. Things do not turn good so easily when they mess up in DB world. 

To my surprise I was not able to a. restore with overwrite d. alter database to single user mode c. take db offline d. drop the database. And as you might have guessed the reason by now the database automatically starts when SQL Server restarts. Well I always loved this feature where you do not have to manually start 100s of database manually or through a script every time you restart SQL Server. But just for this time I was aching to have that feature where I could actually stop the recovery of the databases I do not want. I finally got to the answer through a lot of soul searching and blog posts (Amit Bansal's website ).

So here goes the list of my attempts 

1. Take the SQL Server in single user mode and try to take the user databases offline - Did not work 

2. Take the SQL Server in single user mode and try to drop the user databases - Did not work

3.  Take the SQL Server in minimal  mode and try to take the user databases offline - Did not work 

4. Take the SQL Server in minimal  mode and try to drop the user databases - Did not work

5. Set Trace 3607 in SQL Server Startup and try to take the user databases offline - Did not work 

6. Set Trace 3607 in SQL Server Startup and try to  drop the user databases - Did not work

Then finally I hit gold and tried to set the Trace 3608 in the SQL Server Startup and started the SQL Server. 

Most of the user databases showed online though some were in recovery or recovery pending. 

I dropped the databases which were in online status and restarted the SQL Server  with the trace 3608 in the the startup parameter to bring the other user databases online and then dropped them. 

I finally removed the trace flag 3608 and restarted the SQL Server and all the user databases were gone. 

So all I had to do now was to use the restore script to generate the restore database command for all databases. Well I swung a cursor around the script to get everything in one go but later on that in the next blog post. 

Thanks,

Tushar Kanti