But first: a warning

Deleting a Database is an irreversible action. There is no Ctrl+Z or Undo button. And deleting a Database is a quick and easy action. Much faster than restoring a Database, anyway.

There is only one way to restore the Database and that is when you have a Backup. So always make it a practice to Backup the Database you want to get rid off.

In most cases you will never need it, but just to be sure you have it when needed: always make a Backup first.

Kind reminder: a Backup is only complete once you have verified it can be restored!

Three Ways to get rid of a Database

As you will see, there are different ways to get rid of a Database. These options are there for a reason. For each option I will give you a reason why that option is there and when to use it.

Take Offline

Each Database in SQL Server takes up some of its Resources. If you want to free up Resources by deleting a Database, but you are not 100% sure if anything relies on that Database, check this first. But sometimes documentation isn't up-to-date or even lacking. People who could have given you the information have since long left the company.

If you are pretty sure the Database is no longer necessary but your information is not 100% reliable, start by taking the Database Offline. To do this, right-click the Database, go to Tasks and then Take Offline.

Now maybe there is a Cross Database Query you didn't realize it was still in use. Or maybe there is an application that runs unnoticed in the background that everybody forgot about over the years.

From this moment on, the worst that can happen is that errors occur in places that do rely on that Database. Of course, it would have been better if this was known before you took it Offline. But when there is no documentation, this is the last resort to find it out.

Luckily, the only thing you need to do is bring the Database back Online. The same as you took it Offline. Right-click the Database, go to Tasks and bring it back Online.

With Take Offline your Database is no longer useable but it will still show up in SQL Management Studio. In case you overlooked something crucial, it only takes a few seconds to bring it back Online.

And while it doesn't take up valuable Resources in SQL Server itself, the storage is still in use since the Database is still there.

Detach

Detaching a Database is almost similar to taking it Offline. But there is of course a difference.

When you take a Database Offline, it will still show up in SQL Management Studio and you are just a few clicks away from bringing it back Online. When you Detach a Database, you effectively remove the references to the Database Files: the MDF and LDF.

To Detach a Database, you can right-click the Database, go to Tasks and select Detach.

The Database now no longer shows in SQL Management Studio, but the MDF and LDF Files are still where they were before the Detach.

Now when you realize it was still in use somewhere, you need to Attach the Database again. For this right-click Databases and select Attach.

In the Dialogue Window that will pop up you must then browse and select the MDF File for that Database.

Do you know the exact File Location? No problem! If not, you need to go search for it. Good luck when everybody is panicking because something crucial is no longer working!

Once you know where the MDF File is located, Attaching it is a quick process as well.

Since SQL Server does not know or doesn't care about a detached Database, you can now move the Database Files to a less valuable storage area within your network.

Drop or Delete

Ok, so this is the one with the most risks. Once you delete the Database, it will no longer show up in SQL Management Studio. And the Database Files (MDF and LDF) are deleted.

So after this there is only one way to get the Database back and that is when you have a Backup. But even when you have a recent Backup, do realize that restoring a large database might take some time.

Deleting a Database is easy. Simply right-click the Database and select Delete. In the Dialogue Window that will pop up you can select Close existing connections:

By default it is not selected and it is adviced to leave it like that. If something is overlooked and someone or some application is still using that Database at that moment, this will prevent it from being deleted.

Now that someone might be you because you just checked the content of that Database. You can check that with this SQL Query:

sp_who

This will show you all activity in all Databases:

Once you are sure that the only connected Active Process, you can try to delete the Database again and then select Close existing connections.

Best Practices

It is a good practice to keep your SQL Server clean and get rid of unused Databases that only take up valuable SQL Server Resources and storage. But when you do, it might be a good idea to follow these steps:

Make a Backup
Also make sure you store it in a safe place.
Take it Offline
Leave it Offline for as long as you want.
It is not taking up any Resources.
And next time you will see and remember it.
Maybe then you feel confident is really isn't in use?
Delete the Database
Do this once you are sure it is not in use anywhere.

Leaving the Database Offline for weeks or even months? Just to make sure there isn't a job that runs only monthly? No problem. It does no longer need valuable Resources except storage.

Are the Database Files located on a valuable storage area in your Network? Detach the Database and move the Files to a less valuable storage area in your Network. In that case you do not need to delete the Database any longer. Just the delete the Database Files once you no longer need them.

That's it for today!

Yes, it might seem unnecessary to follow all these steps. If you are sure it is a test Database on your local machine, feel free to do it however you want. But in production, that is a whole different story. In that case, better be safe than sorry!

Did I ever need to bring a Database back Online in a hurry?

Yes, several times. One time there turned out to be some Code in a Stored Procedure that did a Query on the Offline Database. It didn't do anything with the Results, it was just some left-over Code from developing the Stored Procedure years ago. But since that part of the Query failed and no Error Handling was done, the whole Stored Procedure failed. Removing that bit of Code was the solution.

And one time I had a Customer with a Production Database that was misleadingly named TestDB. My assignment was to delete the Test DB. Can you imagine what would have happened if I would have done just that??

Luckily, in that last case I used sp_who first to check and that already gave away there was something odd. Just way too many active processes on that Database. Of course I notified them about my observation and immediately they realized what could have gone wrong.

Ah well, all's well that ends well!

You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!

OffLine, Detach, Drop

So you want to get rid of a Database on your SQL Server that is no longer in use. As is often the case, there are different ways to do it. In this Post I will talk about the different ways to do this and which to use for different scenarios. So you can decide which way is best for your situation.