So it was the Friday before the Memorial Day weekend, and before leaving for the day I decided to follow my own advice and check my backup solution to ensure it was functioning properly. I’m glad that I did, because it had been silently failing for over a week. Now that it has been fixed I want any early warning system.

IDERA has a free tool called the SQL Backup Status Reporter, which has a very simple to use interface that displays the date of the last backup and the date of the last full backup for each of your databases, along with options for flagging databases.

This was the tool I turned to on that fateful Friday. I like that their tool is easy to use, and accomplishes everything in an intuitive GUI. The drawback is that the tool lacks persistent monitoring and alerting capabilities. IDERA does provide this in a commercial application, but it’s probably overkill given that I have one database server with two databases, and zero budget.

Instead, I will be looking to get the same information from a SQL script: Dave Pinal over at SQLAuthority.com has blogged about this and there’s another great guide at MSSQLTips.com.

I’ve worked out the following SQL, which tells me if the most current backup is more than two days old, and will use it as the foundation for a DBMail alert, although it could also be reworked into an Alert as well.

SELECT DB.name AS DatabaseName,  
   ISNULL(CONVERT(NVARCHAR(24),MAX(BS.backup_finish_date),126),'No Backups Exist') AS LastBackupDate 
FROM [master].[dbo].[sysdatabases] DB 
LEFT JOIN [msdb].[dbo].[backupset] BS ON DB.name = BS.database_name
WHERE BS.database_name = DB_NAME()
GROUP BY DB.name
HAVING MAX(BS.backup_finish_date) IS NULL
OR DATEDIFF(DAY,MAX(BS.backup_finish_date),GETDATE()) >= 2

I hope this has helped some of you. If you are looking for an easy way to periodically check the status of your backups without using SSMS, I’d recommend downloading IDERA’s SQL Backup Status Reporter, just prepare yourself for the onslaught of promotional emails.