An alert for Log backup which is less than 2 hours I am looking for a script which will send me an email with the below condition:


Send email when there is no log backup for the last 2 hours.


I actually have generated this output using the below script, but I need my output to have a last full backup date in it.

The script:

DECLARE 
  @dblist NVARCHAR(MAX) = N'', 
  @c CHAR(2) = CHAR(13) + CHAR(10), @t CHAR(1) = CHAR(9),
  @s SYSNAME = @@SERVERNAME;

SELECT @dblist += @c + @c + @s + @c + @t + db + @c + @t
  + COALESCE(CONVERT(CHAR(10), d, 120) + ' ' + CONVERT(CHAR(8), d, 108), 'NULL')
  + @c + @t + CONVERT(VARCHAR(11), age)
FROM
(
  SELECT 
   bs.database_name AS db, 
   MAX(bs.backup_finish_date) AS d, 
   DATEDIFF(HOUR, MAX(bs.backup_finish_date), GETDATE()) AS age 
-------------^^^^ please don't use lazy shorthand like hh
  FROM    msdb.dbo.backupset AS bs 
  WHERE [type] = 'L'  
  GROUP BY database_name 
  HAVING (MAX(backup_finish_date) &lt; DATEADD(HOUR, -2, GETDATE()))  

UNION ALL -- why UNION? By definition there are no duplicates to filter.
          -- in fact you could re-write this without two separate queries at all.

  SELECT
    name AS db,  
    NULL AS d,  
    9999 AS age 
  FROM 
    master.sys.databases AS d
------ don't use sysdatabases - old and deprecated
  WHERE name &lt;&gt; N'tempdb' AND NOT EXISTS 
  (
    SELECT 1 FROM msdb.dbo.backupset 
      WHERE database_name = d.name
  )
) AS x
ORDER BY db;

IF @dblist &gt; N''
BEGIN
  PRINT @dblist;
  EXEC msdb.dbo.sp_send_dbmail @profile_name = 'profile',
    @recipients = 'ABC@ABC.com',
    @subject = N'Databases not backed for last 2 hrs',
    @body = @dblist;
END
The below script will create a SQL Server Agent Job that runs hourly.  The job reports, via database mail, whenever it detects a database that hasn't had a log backup in the past 2 hours.  Since log backups don't apply to databases in the simple recovery model, those databases are excluded.  Also, the model database is excluded - you may want to remove that exclusion if you make frequent modifications to the model database (why would you, though).

The job is schedule to run starting at 6:00 am each day, and stop running at 10:00 pm.  You'll need to modify the @recipients and @profile_name variables in the first couple of lines of the script.

The job is set to notify the SQL Server Agent Operator named 'DBA', so you'll need to set that up in advance.  You can use dbo.sp_add_operator to add an operator via T-SQL.

USE [msdb]
GO
DECLARE @recipients varchar(max) = '&lt;your_email_here&gt;';
DECLARE @profile_name sysname = N'&lt;database_mail_profile_here&gt;';

DECLARE @cmd nvarchar(max) = N'DECLARE @msg nvarchar(max);
SET @msg = N'''';
;WITH src AS 
(
SELECT bs.database_name
FROM msdb.dbo.backupset bs
    INNER JOIN msdb.dbo.backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
WHERE bs.type = ''L'' --only log backups
    AND bs.backup_finish_date &gt; DATEADD(MINUTE, -120, GETDATE())
)
, last_backup AS (
SELECT bs.database_name
    , bs.backup_start_date
    , rn = ROW_NUMBER() OVER (PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC)
FROM msdb.dbo.backupset bs
    INNER JOIN msdb.dbo.backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
WHERE bs.type = ''L'' --only log backups
    AND bs.backup_finish_date &lt;= DATEADD(MINUTE, -120, GETDATE())
)
SELECT @msg = @msg + CASE WHEN @msg = N'''' THEN N'''' ELSE N''&lt;/td&gt;&lt;/tr&gt;&lt;tr&gt;&lt;td&gt;'' END 
    + d.name + CASE WHEN lb.backup_start_date IS NOT NULL AND lb.backup_start_date &lt;&gt; N'''' THEN N'' (last log backup: '' + CONVERT(nvarchar(30), lb.backup_start_date, 120) + N'')'' ELSE N'''' END 
FROM sys.databases d
    LEFT JOIN last_backup lb ON d.name = lb.database_name AND lb.rn = 1
WHERE d.state_desc = N''ONLINE''
    AND d.recovery_model_desc &lt;&gt; N''SIMPLE'' --simple recovery affords no log backups
    AND d.name &lt;&gt; CONVERT(sysname, N''model'')
    AND NOT EXISTS (
        SELECT 1
        FROM src
        WHERE src.database_name = d.name
        )
ORDER BY d.name;

IF @msg &lt;&gt; N''''
BEGIN
    SET @msg = N''&lt;html&gt;
    &lt;style&gt;body {
        font-family:Segoe UI,Verdana,Tahoma;
        font-size:10pt;
    }
    &lt;/style&gt;
    &lt;body&gt;
    &lt;p&gt;The following databases haven''''t had a log backup during the last 2 hour period:&lt;/p&gt;&lt;br /&gt;
    &lt;table&gt;&lt;td&gt;&lt;tr&gt;'' + @msg + N''&lt;/td&gt;&lt;/tr&gt;&lt;/table&gt;
    &lt;/body&gt;
    &lt;/html&gt;'';
    DECLARE @subject nvarchar(1000);
    SET @subject = ''Log Backup Notification from SQL Server: '' + @@SERVERNAME;

    EXEC msdb.dbo.sp_send_dbmail
          @profile_name = ''' + @profile_name + N'''
        , @recipients = ''' + @recipients + N'''
        , @subject = @subject
        , @body = @msg
        , @body_format = ''HTML'';
END
';

DECLARE @ReturnCode int;
SELECT @ReturnCode = 0;

BEGIN TRANSACTION;

BEGIN TRY
    IF NOT EXISTS (
        SELECT sc.name 
        FROM msdb.dbo.syscategories sc
        WHERE sc.name = N'Log Notifications' AND category_class=1
        )
    BEGIN
        EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N'JOB'
            , @type = N'LOCAL'
            , @name = N'Log Notifications'
        IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) THROW 50000, N'An error occurred adding the Job Category', 1;
    END

    DECLARE @jobId binary(16);

    IF EXISTS (
        SELECT 1
        FROM msdb.dbo.sysjobs sj
        WHERE sj.name = CONVERT(sysname, N'Log Backup Notifier')
        )
    BEGIN
        EXEC msdb.dbo.sp_delete_job @job_name = N'Log Backup Notifier'
            , @delete_history = 0
            , @delete_unused_schedule = 0;
    END

    EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Log Backup Notifier' 
        , @enabled = 1
        , @notify_level_eventlog = 3
        , @notify_level_email = 2
        , @notify_level_netsend = 0
        , @notify_level_page = 0
        , @delete_level = 0
        , @description = N'No description available.'
        , @category_name = N'Log Notifications'
        , @owner_login_name = N'sa'
        , @notify_email_operator_name = N'DBA'
        , @job_id = @jobId OUTPUT;

    IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) THROW 50000, N'An error occurred adding the Job', 1;

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId
        , @step_name=N'Email DBAs if no log backup has occurred in 2 hours'
        , @step_id = 1
        , @cmdexec_success_code = 0
        , @on_success_action = 1
        , @on_success_step_id = 0
        , @on_fail_action = 2
        , @on_fail_step_id = 0
        , @retry_attempts = 0
        , @retry_interval = 0
        , @os_run_priority=0, @subsystem = N'TSQL'
        , @command = @cmd
        , @database_name = N'msdb'
        , @flags = 0;

    IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) THROW 50000, N'An error occurrred adding the Job Step', 1;

    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId
        , @start_step_id = 1;

    IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) THROW 50000, N'An error occurred setting the job starting step.', 1;

    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId
        , @server_name = N'(local)';

    IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) THROW 50000, N'An error occurred setting the job server to (local)', 1;

    IF EXISTS (
        SELECT 1
        FROM msdb.dbo.sysschedules ss
        WHERE ss.name = N'Log Notification Schedule'
        )
    BEGIN
        EXEC msdb.dbo.sp_delete_schedule @schedule_name = N'Log Notification Schedule';
    END
    DECLARE @start_date int;
    SET @start_date = CONVERT(int, CONVERT(varchar(8), GETDATE(), 112));
    EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @jobId
        , @name = N'Log Notification Schedule'
        , @enabled = 1
        , @freq_type = 4
        , @freq_interval = 1
        , @freq_subday_type = 8
        , @freq_subday_interval = 1
        , @freq_relative_interval = 0
        , @freq_recurrence_factor = 0
        , @active_start_date = @start_date
        , @active_end_date = 99991231
        , @active_start_time = 60000
        , @active_end_time = 215959;

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF (@@TRANCOUNT &gt; 0) 
    BEGIN
        ROLLBACK TRANSACTION;
        THROW 
    END
END CATCH
GO