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 .
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:
- Set the database to Read-Only Mode.
- Perform a Full Database Backup.
- Take the database Offline.
- Detach the database from the SQL Server instance.
- 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 heading…just kidding … 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 . 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 .
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 . 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.