SQL SERVER – Know Your Backup Before Deleting Database

Earlier last week I wrote a blog around, SQL SERVER – FIX – Msg 3702, Level 16, State 3 – Cannot Drop Database “DB_Name” Because it is Currently in Use. The premise of that blog was driven by some of the demo’s I show at conferences. During one of the UG Meets, I met my good friend Balmukund doing something different and that inspired me to write that blog.

When I wrote that, many people did write back stating it can be dangerous etc. I sort of agree if you are on a production box. As my usecase was based on Demo environment for sessions, lesser did I think about it. Having said that, immediately I thought it would be good to back-it-up with a blog that will prevent us from getting into trouble.

So I wrote back to my friend who wrote an email to me about the dangers. Yes, we need to be careful while dropping databases but be prepared for contingencies. If you have taken a backup of your database, make sure to check if the same is available by querying the MSDB. I gave a typical script would look like this:

CASE msdb..backupset.TYPE
'D' THEN 'Database'
WHEN 'I' THEN 'Differential'
WHEN 'F' THEN 'Filegroup'
END AS backup_type,
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset
ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
-- Add the WHERE condition if you want it for a specific database
-- WHERE msdb.dbo.backupset.database_name = 'AdventureWorks2014'
ORDER BY msdb.dbo.backupset.backup_finish_date


The above script is a classic way to find all the backups done for a given database or the databases on a given instance.

As a best practice, I would like to figure out from these DMV’s if any backups were taken on every single database before working on them. I am sure as a seasoned DBA, you all are always aware of this important steps when working on production databases.

I know each organization has a restore strategy, so can you let me know about your restore strategies for critical databases? When do you take FULL, Differential and how often you take TLog backups in your production environments? Sharing this is a great way to tell the blog readers about typical usage patterns.

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

SQL Backup and Restore
Previous Post
Interview Question of the Week #023 – Error Handling with TRY…CATCH
Next Post
SQL SERVER – Puzzle with Year Function

Related Posts

5 Comments. Leave new

  • That’s a great point and we had a healthy discussion around it at our last UG meetup. In fact, if you have an important database, you should automate the backup and verification of those backups. As Scott Hanselman says “Backups always succeed. It’s restores that fail”! I certainly wouldn’t drop an important database before having tested a restore.

    • That is a valid point. Restore has to be tested too, I do advocate a lot of this to folks who talk to me !!! Thanks for bringing this up.

  • That’s really sounds good… we always should have a backups to survive from a crash of database or droping a imp database

  • How to make sure that we have good backup, when we have more than 1000+ production databases on 200+ servers, getting full backup every night 8 p.m. ? Is there a way to automate the restore process of all databases in all servers through linked servers, just for testing purpose that we have good backup.


Leave a Reply