Log Backup Alert

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

Parents
  • use msdb;

    go

    /*

    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

Reply Children
No Data