Deleting a Database!!! Did you take a Backup?


We all took backup of our databases, that’s a habit now rather than a task, correct?? However, did you take a backup before dropping a database? If not, you might consider to take a Full database backup before deleting a database. In this post, I am going to list out certain steps, which I follow before removing a database. I will not say this is the best way to do it, but many time, it became a Life saver Winking smile.

Learning never ends, everyday we learn something new. I learned it from my current & previous assignments. Before dropping a database from your SQL Server instance, consider performing the following steps:

  1. Set the database to Read-Only Mode.
  2. Perform a Full Database Backup.
  3. Take the database Offline.
  4. Detach the database from the SQL Server instance.
  5. Delete the data & transaction log files manually (in a later date if possible).

Ok, I understand, what you want to say? If you have to drop a database, why do you  may want to follow these steps rather than simply dropping it, correct?? Well, it means you didn’t read the headingjust kidding Smile… I am talking about BACKUP. I faced this issue personally many times, during my current and previous assignments. Two common reasons, why I follow the above steps are:

  • I was asked to drop a database. After I dropped it, the team wants it back because they sent me the wrong name of the database Sad smile Sad smile Sad smile. Trust me, you don’t want to be in that seat !!!
  • I was asked to drop  a database; however, after I completed the task, I received another request to bring the database online ASAP, because of XYZ…. reasons Sad smile Sad smile.

We all know the steps to perform. Find the last Full backup of the database, along with the log backups (only if the database is not in SIMPLE Recovery Model and you took log backup earlier). Restore the database from the existing backups.

Now, take a look on the above five steps, and think, what you have to do for restoring the database. If you didn’t remove the data and t-log files immediately, then probably you can restore the database faster than any other means Smile. I mean, personally, I feel this might be the best way to remove a database from a SQL Server instance. One more Full Backup of that database and to keep the files for a day or two, may not be a big issue, until and unless, it’s a compliance issue.

This is my personal opinion, I find it useful many times. What steps did you follow before deleting a database? Do post your feedback. Smile

Advertisements

About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in Backup - Restore, SQL Server and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s