OFFLINE Database files not deleted when DROP DATABASE is executed

 A DBA contacted me in a panic this afternoon. They’d accidentally dropped a database . We had a brief conversation about whether a valid backup existed , and if it did exist can we restore the database.

It turned out there was no valid backup in place.  During the course of our conversation we established the  database status prior to the DROP database statement and any other information which may be useful in recovering the database.

It emerged , the DBA had placed the database in an OFFLINE status prior to the DROP DATABASE statement. I remembered reading in SQL Server books online, if the database is OFFLINE when the DROP database statement is executed , the disk files are not deleted.

As opposed to when the database is ONLINE, if a DROP database command is executed the physical files are also deleted from the Windows file system.

That was one relieved DBA. He was able to issue a ATTACH statement and restore the database back to it’s ONLINE status

Try this for yourself. Make sure it is a test database!

CREATE DATABASE offline_test 
ALTER DATABASE offline_test SET OFFLINE
DROP DATABASE offline_test

 Read More on database status

SQL Database Status with sys.databases - SQL Server DBA

SQL Server Database SUSPECT state - SQL Server DBA

SQL Server Database Status,monitoring and Restores - SQL Server
...