DESCRIPTION
This alert will detect databases that have not had a backup done within the last day. It will provide a simple count, and any return of 1 or more (at least one backup for a database has failed) will trigger an email.
ALERT DEFINITION
To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Single Numeric Return. Configure the alert with values similar to these:
Alert Name: Missing backups
Execution Interval: 1 day
Notification Text: This instance has databases not backed up in the last day.
Active Database Instances: Add the Ignite Repository DB only
SQL Statement:
SELECT count(*)
FROM sysdatabases
WHERE name NOT IN (
SELECT database_name
FROM msdb..backupset
WHERE type = 'D'
GROUP BY database_name
HAVING max(backup_start_date) > GETDATE()-1)
AND name NOT like 'tempdb'
AND name NOT like 'model'
Execute Against: Monitored Instance
Units: Number of databases without backup
High Threshold: Min 1, Max empty
