Log Backup Alert

Can you please help me how to setup an alert about no log backup for a database in full recovery ?

  • use msdb;



    This will provide a boolean value of 'True' or 'False' if the lastest TLog backup for specific

    database is not withing the configured threshold. You would then configure the custom alert as a True/False

    This only works for one database at a time.

    A value of TRUE would mean the last LOG backup is older than your threshold

    if you need to monitor MINUTES instead of HOURS, change the value in the DATEDIFF statement


    DECLARE @Threshold int = 4 -- number of hours

    DECLARE @DBName varchar(100) = 'AfsAudit_Iberia' --- name of database you want to monitor

    select top 1

    datediff(MINUTE, b.backup_finish_date, getdate())

    ,case when datediff(HOUR, b.backup_finish_date, getdate()) > @Threshold then '1' else '0' end [NoLog]

    from dbo.backupset b

    where b.type='L' and b.database_name=@DBName

    order by b.backup_finish_date DESC