What Backups Do I Have?

The #1 priority of a DBA is to make sure they can recover a database should it become corrupted.  To recover a database, you need to make sure you have backups.

Is knowing you have a backup process that is scheduled to run enough to make sure you have backups? No! I’ve actually experienced a situation where my backup jobs were running every night, but were not creating the backups correctly. Worse yet, I wasn’t notified.

Therefore, as a DBA you need to check periodically to make sure you have backups. I would recommend you do this daily, if you are taking backups daily.

Below is a TSQL script that will identify all the database backups, their start and finish times, and the type of backups that were taken in the last 7 days.

SELECT b.database_name, 

       b.backup_start_date, 

       b.backup_finish_date, 

       CASE b.type 

       WHEN ‘D’ THEN ‘Database’

       WHEN ‘I’ THEN ‘Differential’

       WHEN ‘L’ THEN ‘Log’

       END AS backup_type, 

       f.physical_device_name

 FROM msdb.dbo.backupmediafamily f

 INNER JOIN msdb.dbo.backupset b ON f.media_set_id = b.media_set_id 

 WHERE b.backup_start_date > GETDATE() 7

 ORDER BY b. backup_finish_date  desc;

 

Don’t be caught without a critical database backup.  Have a routine that periodically checks for backups using this script or something similar to the one above, then review the output.  By doing this you can make sure you have backups, so you can recover your database if needed.

 
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Latest Articles