Following my last post (Setting up Database Mail to use my Gmail account) where Database Mail was set up to send to a GMail account, this post looks at how to send reports in HTML direct to a mailbox using just a stored procedure. To make life easier, also adding colour to highlight any issues.

I create a number of these reports when starting a new project, monitoring various areas from database backups and overnight data warehouse load, to numbers of expired rows in slowly moving dimensions. These are delivered to my inbox at the start of the day so I know all is well and can move on to more interesting tasks.

The stored procedure below sends a backup report to anyone specified in the @to input parameter (if you want to send to more than one address, separate them using a semi-colon).  Feel free to edit it as you please to meet your requirements – for example you might want to exclude any databases that are offline or acting as a secondary partner in Database Mirroring.

use [master];
CREATE PROCEDURE dbo.BackupReport (
  @to nvarchar(128) = null
, @LogThresholdInHours int = 1
, @DiffThresholdInHours int = 24
, @FullThresholdInHours int = 168
/*SQL Server database backup report
Last updated 5 February, 2017

Mat Hayward-Hill */
set nocount on;
    declare @tableHTML nvarchar(MAX);
    declare @footerHTML nvarchar(MAX);
    declare @subject nvarchar(200);
    declare @red char(7) = '#FF7277';
    declare @green char(7) = '#E4FEDB';

    set @footerHTML = '<footer><p style="font-size:11pt; font-family:calibri;color:#999C96">Report sent from '+@@SERVERNAME+' created on '+CONVERT(varchar, GETDATE(),120)+'</p></footer>'
    set @subject = 'Database backup report';

    with [backups] as
    , 	case [type]
        	 when 'D' then 'Database'
   when 'I' then 'Differential database'
   when 'L' then 'Log'
   when 'F' then 'File or filegroup'
   when 'G' then 'Differential file'
   when 'P' then 'Partial'
   when 'Q' then 'Differential partial'
        end as "backup_type"
    ,	[backup_finish_date] 
    ,    RANK() over (partition by [database_name], [type] order by [backup_finish_date] desc) as 'rownum'
         [server_name] = @@SERVERNAME
    ,   d.[recovery_model_desc]
    ,   b.[backup_type]
    ,   b.[backup_finish_date]
    into #backups
        sys.databases d
    left join
        [backups] b
        on(b.[database_name] = d.[name] and b.[rownum] = 1)
         d.[name] not in ('model','tempdb');

    if(@to is not null)
        set @tableHTML =
        N'<H4 style="font-family:calibri">Database Backups</H4>' +
        N'<table border="1" style="font-size:11pt; font-family;calibri">' +
        N'<tr><th align="left">Database Name</th>' +
        N'<th align="left">Recovery Model</th>' +
        N'<th align="left">Backup Type</th>' +
        N'<th align="left">Backups</tr>' +
                      when [backup_type] is null then @red 
                      when [backup_type] = 'Log' and [backup_finish_date] < DATEADD(hour, @LogThresholdInHours * -1, GETDATE()) then @red
                      when [backup_type] = 'Differential database' and [backup_finish_date] < DATEADD(hour, @DiffThresholdInHours * -1, GETDATE()) then @red
                      when [backup_type] = 'Database' and [backup_finish_date] < DATEADD(hour, @FullThresholdInHours * -1, GETDATE()) then @red
                      else @green
                  end as [@bgcolor],
                  td = [name], '',
                  td = [recovery_model_desc], '',
                  td = COALESCE([backup_type],'No Backup!'), '',
                  td = COALESCE([backup_finish_date],''), ''
              for xml path('tr'), type
              ) as nvarchar(MAX)
        ) + N'</table>';

        set @tableHTML = @tableHTML + @footerHTML
        exec msdb.dbo.sp_send_dbmail
            @profile_name = 'Database Mail profile'
        ,   @recipients = @to
        ,   @body_format = 'HTML'
        ,   @subject = @subject
        ,   @body = @tableHTML;
         select * from #backups

Once you have implemented the stored procedure you can setup a job to execute the code below to have the report delivered daily.

exec dbo.BackupReport ''

SQL Server HTML Backup Report

Creating a HTML SQL Server Backup Report
Tagged on:     
Follow on Feedly
%d bloggers like this: