One of the most important duties of a production DBA is to ensure backups are happening regularly. No one wants to face a manager and tell him/her “I’m sorry, but the most recent backup I have is from a week ago”. Create a SQL backup report, so you can review your situation!
My production environment is rather large, so it would be easy to rely on backup failure alerts. Don’t fall into that trap!
You are trusting that both your backup system, your notification system AND your email system are all in working order, and that’s a lot of trust. I much prefer being proactive, which includes verifying manually (as often as i can afford) that my backups ran.
To address that need, I wrote the following script. Once a week, I run it against a Registered Server list (you have one of those, right?). After a few seconds, you will have a complete list with the last time each database was backed up, as well as backup type, and age of the backup.
SELECT DISTINCT DatabaseNames.NAME AS NAME,
Max(bs.backup_start_date) AS
[Latest Backup],
Datediff(dd, Max(bs.backup_start_date), Getdate()) AS
[Backup Age],
CASE
WHEN type = 'D' THEN 'Full'
WHEN type = 'I' THEN 'Differential'
WHEN type = 'L' THEN 'Log'
ELSE 'Some other type'
END AS
Backup_Type,
CASE
WHEN Max(bs.backup_start_date) IS NULL THEN 'NEEDS BACKUP'
WHEN Datediff(dd, Max(bs.backup_start_date), Getdate()) > 1
THEN
'NOT UP TO DATE'
WHEN Datediff(dd, Max(bs.backup_start_date), Getdate()) = 1
THEN
'DATABASE BACKED UP'
WHEN Datediff(dd, Max(bs.backup_start_date), Getdate()) = 0
THEN
'DATABASE BACKED UP'
ELSE 'PLEASE REVIEW MANUALLY'
END AS Status
FROM sys.databases AS DatabaseNames
LEFT OUTER JOIN msdb.dbo.backupset AS bs
ON bs.database_name = DatabaseNames.NAME
WHERE databasenames.NAME NOT IN ( 'tempdb', 'reportservertempdb' )
GROUP BY DatabaseNames.NAME,
type
HAVING Datediff(dd, Max(bs.backup_start_date), Getdate()) > 0
--more than 1 day since last backup
ORDER BY NAME,
backup_type
From here, there’s a couple of directions you could go. You could save the output somewhere, if that’s part of your audit requirements! Or you could automate this report to yourself, as long as you have enough discipline to still review it… I find that emails are easy to ignore when you get > 100 emails/day, so if that’s you, I would keep on running it manually.