Earlier this week, we discussed the issue of not being able to drop a database in SQL Server when it is currently in use. While this may not be a concern in a demo environment, it can be a dangerous situation in a production environment. To prevent such issues, it is important to have a backup strategy in place.

One way to ensure that you have a backup of your database is by querying the MSDB database. By using the following script, you can find all the backups done for a given database or the databases on a specific instance:

USE MSDB
GO

SELECT msdb.dbo.backupset.database_name,
       msdb.dbo.backupset.backup_start_date,
       msdb.dbo.backupset.backup_finish_date,
       CASE msdb..backupset.TYPE
           WHEN 'D' THEN 'Database'
           WHEN 'L' THEN 'Log'
           WHEN 'I' THEN 'Differential'
           WHEN 'F' THEN 'Filegroup'
       END AS backup_type,
       msdb.dbo.backupmediafamily.physical_device_name
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

This script provides a comprehensive view of all the backups performed on your databases. It is a best practice to check if any backups were taken on every single database before making any changes to them.

As a seasoned DBA, you are likely already aware of the importance of having a restore strategy in place for critical databases. It would be great if you could share your restore strategies with our blog readers. When do you take full backups? How often do you take differential backups? And how frequently do you perform transaction log backups in your production environments? Sharing this information can help others understand typical usage patterns and learn from your experiences.

Remember, having a solid backup strategy is crucial for ensuring the availability and integrity of your data. It is always better to be prepared for contingencies rather than facing the consequences of data loss.

Stay tuned for more SQL Server tips and best practices!