dcsimg
 

What Backups Do I Have?

Wednesday May 12th 2021 by Greg Larsen

As a DBA you need to check periodically to make sure you have backups.  I would recommend you do this daily.

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.

 
Mobile Site | Full Site