So something that you should do regardless of the alerts, notifications, etc that you have setup is to just have a daily sanity check on your database backups. Sure you don’t have any failed jobs but what if the job never attempted to run in the first place like it was supposed to?
If a database fails in the enviroment and no backup is there to recover from does it make a sound?
Well no… but the users are heard for miles hahaha
Below is a nice quick sanity check showing all the dbs that have NOT been backed up for X days or have never been backed up at all.
DECLARE @num_of_days INT
SET @num_of_days = 1
SELECT A.name, MAX(B.backup_finish_date) AS 'LastBackupDateTime'
FROM master.dbo.sysdatabases A WITH(NOLOCK) LEFT OUTER JOIN
msdb.dbo.backupset B WITH(NOLOCK) ON A.name = B.database_name
WHERE (B.TYPE = 'D' OR B.TYPE IS NULL)
GROUP BY A.name
HAVING (MAX(B.backup_finish_date) < GETDATE() - @num_of_days OR MAX(B.backup_finish_date) IS NULL)
ORDER BY A.name
Enjoy!!
Thanks for the tip. Check your post as some of the code is hidden behind your categories sidebar.
hmmm it doesn’t appear so on my side. Perhaps it’s a browser or screen resolution issue?
I’ll ping some friends to give it a try but just curious… what browser/resolution are you at?
Thanks
Thanks, helpful script.
[...] SQL Databases That Have Not Had a Full Backup in x Days [...]