Should an OFFLINE database be SET EMERGENCY before dropping?

Question

As a rule before dropping a database I, SET OFFLINE, for 30 days before dropping it. If there is anything that was missed and the database is still needed, I can SET ONLINE quickly for a full restore.

USE master
GO
ALTER DATABASE database_name
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

If you drop a database while it is offline, the files stay on the server forever unless you purposely go clean them up. Assuming the you have taken whatever backups you want and are sure you want to completely drop the database on the server, you put it back online then drop it.

USE [master]
GO
ALTER DATABASE database_name
SET ONLINE

DROP DATABASE database_name
GO

The database comes fully back online then gets dropped. Might it be a better practice to SET EMERGENCY before dropping? Depending on many variables, in theory when you go online a missed OLTP connection could access the database between ONLINE and DROP.

Using SET EMERGENCY, grants "READ_ONLY to members of sysadmin and brings the database far enough online so that files on the server are deleted. At least with my limited testing on SQL2014

USE [master]
GO
ALTER DATABASE database_name
SET EMERGENCY

DROP DATABASE database_name
GO

If a missed OLTP (or anything) manages to connect to the database between ONLINE and DROP you would have deleted any record of it with the drop.

Might it be a better practice with an OFFLINE database to SET EMERGENCY before dropping? If you are using T-SQL is it to quick for any possible connection to occur?

If you are using the Delete Option on SSMS, even best case between online and delete, is going to measurable in seconds. Should this always be avoided?

Answer 1

Note: make sure you have a final full backup of the database before you drop it, just incase if you need to restore.

Normally, EMERGENCY mode is used for getting your database out of corruption.

You are right, that EMERGENCY mode will allow only sysadmin members and the db will be read-only - nothing gets written to T-LOG.

I would suggest that get the database to RESTRICTED USER and then drop it.

e.g. ALTER DATABASE your_db_name SET RESTRICTED_USER or READ_ONLY

Note: This is just my preference which distinguish between EMERGENCY mode --> use to take DB out of corruption vs RESTRICTED_USER or READ_ONLY --> give it chance to breathe before you drop !

Answer 2

When you drop a database set to EMERGENCY mode it will leave the log files behind. RESTRICTED_USER doesn't help if the users have the db_owner or dbcreator role. So I would go with ALTER DATABASE your_db_name SET READ_ONLY as well.