Backup overdue alert and differential backup

gerhau

Hi

What is the reason that the alert "backup overdue" will not also check for diff backups?
If you do not want to implement it for that alert, then at least there should be one that checks for both diff and full backups?
As it is now, for the databases where we take a full backup once a week, and diff backup every day/multiple times pr day, we will not get a message if the diff backup has not run (since the alert needs to be set for 7 days).
We use simple recovery model.

br
Gert

wendy.mitchell

Hi Gert

Currently there is not an alert for the differential backup failures, however it would be possible to setup a custom metric to return an integer to be able to alert you.

An example of this maybe to return a count of databases that are missing a differential backup in the last 24 hours e.g.

Select count(*)
from sys.databases d
Where d.name <> 'tempDB'
And d.state = 0 -- online
And d.source_database_id Is Null
And Not Exists (
    Select 1 From msdb.dbo.backupset
    Where database_name = d.name
    And type IN ('D', 'I')
    And DateDiff(hh, backup_finish_date, getdate()) < 24);

We have also raised it as an enhancement in jira with the reference of SRP-9583.

You are welcome to raise this on the SQL Monitor Uservoice at the following link:

User Forum

Many Thanks

Kind Regards
Wendy Mitchell
Product Support Engineer