OFFLINE Database files not deleted when DROP DATABASE is executed

02 February,2015 by Jack Vamvas

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on OFFLINE Database files not deleted when DROP DATABASE is executed


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer