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.