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]; GO 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 http://fourteen33.uk */ as set nocount on; begin 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 ( select [database_name] , 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' from msdb.dbo.[backupset] where [server_name] = @@SERVERNAME ) select d.[name] , d.[recovery_model_desc] , b.[backup_type] , b.[backup_finish_date] into #backups from sys.databases d left join [backups] b on(b.[database_name] = d.[name] and b.[rownum] = 1) where d.[name] not in ('model','tempdb'); if(@to is not null) begin 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>' + CAST((select case 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],''), '' from #backups 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; end else select * from #backups end GO
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 'email@example.com'