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

Question: Can You DROP Offline Database?

Answer: Ofcourse Yes.

Before you jump and say you knew this one. Well, super. As I always say you know your stuff.

Now for the rest of the people who did not know about this, let me give some context behind this question.

I have heard this question multiple times in recent interviews which I help conduct an organization. Everytime when this question was asked, most of the DBAs and users answered that – “As offline databases are not accessible, they can’t be deleted.”

The reality is that most of the users are confused with the word OFFLINE. When people hear the word “Offline”, they automatically assume that the it makes that particular device inaccessible. This is also true in most of the cases. For example, when the network or internet is offline, it is not accessible.

However, for SQL Server, when it is not offline, it also does not allow you to access the database. To see the error, let us first create the database and attempt to access it.

-- Create Database
CREATE DATABASE [TestDB]
GO
-- Take the Database Offline
ALTER DATABASE [TestDB] SET OFFLINE
WITH ROLLBACK IMMEDIATE
GO
/*Now try to access the database it will give you error*/

You can see in the above error that it clearly tells us that the database is not accessible.

However, if you run the following script, it will drop the database successfully.

DROP DATABASE [TestDB]
GO

So in SQL Server, we can DROP any OFFLINE database if we have sufficient permissions.

Reference: Pinal Dave (https://blog.sqlauthority.com)