Can You DROP Offline Database? – Interview Question of the Week #144

Question: Can you drop a SQL Server database while it is offline?

Answer: Yes. The word offline makes this sound like a trick, and it has caught more than one candidate in interviews I have helped conduct. They reason that because a user cannot connect to an offline database, nobody can drop it. SQL Server still knows about the database through the instance, so an authorized administrator can issue DROP DATABASE from master.

Here is a disposable lab example. Do not substitute a production database name. The queries before the drop deliberately show both the offline state and the file locations, because there is a second part to this answer that is easy to miss.

USE master;
IF DB_ID(N'IQ144_DropLab') IS NOT NULL
    THROW 50138, 'Lab database already exists. Do not reuse it.', 1;
CREATE DATABASE [IQ144_DropLab];
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(N'IQ144_DropLab');
ALTER DATABASE [IQ144_DropLab]
SET OFFLINE WITH ROLLBACK IMMEDIATE;
SELECT name, state_desc
FROM sys.databases
WHERE name = N'IQ144_DropLab';
DROP DATABASE [IQ144_DropLab];

The state query returns OFFLINE before the drop. The DROP DATABASE statement removes the database from the SQL Server instance, assuming the login has the required permissions. This is the narrow answer to the interview question. Notice that the command runs while the connection is in master, not in the offline database.

What about the files? This is where a one-word “yes” becomes a poor operational answer. According to Microsoft’s DROP DATABASE documentation, if the database or any of its files is offline when it is dropped, SQL Server does not delete the database files from disk. That is why the example records physical_name first. After the drop, verify the exact paths and your recovery requirements before deciding what to do with those files. Do not treat them as automatically cleaned up.

So when I ask this in an interview, I am listening for two things: yes, SQL Server can drop an offline database; and no, dropping it in that state is not necessarily the end of the storage work. That second sentence tells me the candidate has thought past the syntax.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.