Subscribe to this blog
Briefcase
Print
As part of our DBA life , we have to configure many alert or statistical mails which gives an idea about the state of the database server. Let us discuss how we can send an email in HTML table format using TSQL. I am just going to give a sample script and it is self explanatory.
USE MSDBGODECLARE @Reportdate DATESET @Reportdate =CONVERT(VARCHAR(10),GETDATE(),121)
/************************************************************** full backup Header***************************************************************/
DECLARE @FullBackupHeader VARCHAR(MAX)SET @FullBackupHeader='<font color=black bold=true size= 5>'SET @FullBackupHeader=@FullBackupHeader+'<BR /> Full Backup Report<BR />' SET @FullBackupHeader=@FullBackupHeader+'</font>'/************************************************************** full backup report Section***************************************************************/DECLARE @FullBackupTable VARCHAR(MAX) SET @FullBackupTable= CAST( ( SELECT td = name + '</td><td>' + BackupType + '</td><td>'+ FileName + '</td><td>' + Startdate + '</td><td>' + FinishDate+ '</td><td>' + Duration + '</td><td>' +BackupSize+ '</td><td>' +CompressionRatio FROM (
SELECT sd.name, ISNULL(db.[Backup Type],'0') AS [BackupType], ISNULL(DB.Physical_device_name,'No Backup') AS 'FileName', CAST(ISNULL(DB.backup_start_date,'1900-01-01') AS VARCHAR(24)) AS Startdate , CAST(ISNULL(DB.backup_finish_date,'1900-01-01') AS VARCHAR(24)) AS FinishDate, CAST(ISNULL(DB.Duration,'0') AS VARCHAR(24)) AS Duration, LEFT(CAST(ISNULL(Backupsize,0)AS VARCHAR(100)),4)+' GB' AS BackupSize, LEFT(CAST(ISNULL(ratio,0)AS VARCHAR(100)),5)+'%' AS CompressionRatio FROM SYS.SYSDATABASES sd LEFT JOIN ( SELECT bm.media_Set_id, 'FullBackup' AS 'Backup Type', bm.Physical_device_name , backup_start_date, backup_finish_date, Duration = CONVERT(VARCHAR(5),DATEDIFF(second,backup_start_date,backup_finish_date)/60) + ':' + RIGHT('00' + CONVERT(VARCHAR(5),DATEDIFF(second,backup_start_date,backup_finish_date)%60),2), database_name, ROUND((compressed_backup_size)/1024/1024/1024,2) AS Backupsize , 100-(compressed_backup_size*100/backup_size) AS ratio FROM msdb..backupmediafamily BM INNER JOIN msdb..backupset bs ON bm.media_Set_id = bs.media_Set_id WHERE [type]='D' AND backup_start_date>=DATEADD(dd,-1,@Reportdate) AND backup_start_date<=@Reportdate ) db ON sd.name=db.database_name ) AS d ORDER BY BackupType FOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX) )
SET @FullBackupTable= '<table cellpadding="0" cellspacing="0" border="1">' + '<tr><th width="10">Database Name</th> <th width="20">Backup Type</th> <th width="80">File Name</th> <th width="100">Start Date</th> <th width="40">Finish Date</th> <th width="40">Duration</th> <th width="10">Backup Size</th> <th width="40">Compression Ratio</th></tr>' + REPLACE( REPLACE( @FullBackupTable, '<', '<' ), '>', '>' ) + '</table>' /************************************************************** differential backup Header***************************************************************/DECLARE @DiffBackupHeader VARCHAR(MAX)SET @DiffBackupHeader ='<font color=black bold=true size= 5>'SET @DiffBackupHeader =@DiffBackupHeader +'<BR /> Differential Backup Report<BR />' SET @DiffBackupHeader =@DiffBackupHeader +'</font>'/************************************************************** Differential backup Section***************************************************************/DECLARE @DiffBackupTable VARCHAR(MAX) SET @DiffBackupTable= CAST( ( SELECT td = name + '</td><td>' + BackupType + '</td><td>'+ FileName + '</td><td>' + Startdate + '</td><td>' + FinishDate+ '</td><td>' + Duration + '</td><td>' +BackupSize+ '</td><td>' +CompressionRatio FROM ( SELECT sd.name, ISNULL(db.[Backup Type],'0') AS [BackupType], ISNULL(DB.Physical_device_name,'NO BACKUP') AS 'FileName' , CAST(ISNULL(DBB.backup_start_date,'1900-01-01') AS VARCHAR(24))AS Startdate , CAST(ISNULL(DB.backup_finish_date,'1900-01-01') AS VARCHAR(24)) AS FinishDate, CAST(ISNULL(DB.Duration,'0') AS VARCHAR(24)) AS Duration, LEFT(CAST(ISNULL(Backupsize,0) AS VARCHAR(100)),6)+' MB' AS BackupSize, LEFT(CAST(ISNULL(ratio,0)AS VARCHAR(100)),5)+'%' AS CompressionRatio FROM SYS.SYSDATABASES sd LEFT JOIN ( SELECT bm.media_Set_id, 'Differential Backup' AS 'Backup Type', bm.Physical_device_name , backup_start_date, backup_finish_date, Duration = CONVERT(VARCHAR(5),DATEDIFF(second,backup_start_date,backup_finish_date)/60) + ':' + RIGHT('00' + CONVERT(VARCHAR(5),DATEDIFF(second,backup_start_date,backup_finish_date)%60),2), database_name, ROUND((compressed_backup_size)/1024/1024,2) AS Backupsize , 100-(compressed_backup_size*100/backup_size) AS ratio FROM msdb..backupmediafamily BM INNER JOIN msdb..backupset bs ON bm.media_Set_id = bs.media_Set_id WHERE TYPE='I' AND backup_start_date>=DATEADD(dd,-1,@Reportdate) AND backup_start_date<=@Reportdate ) db ON sd.name=db.database_name ) AS d ORDER BY BackupType FOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX) )
SET @DiffBackupTable= '<table cellpadding="0" cellspacing="0" border="1">' + '<tr><th width="10">Database Name</th> <th width="20">Backup Type</th> <th width="80">File Name</th> <th width="100">Start Date</th> <th width="40">Finish Date</th> <th width="40">Duration</th> <th width="10">Backup Size</th> <th width="40">Compression Ratio</th></tr>' + REPLACE( REPLACE( @DiffBackupTable, '<', '<' ), '>', '>' ) + '</table>'
/************************************************************** Empty Section for giving space between table and headings***************************************************************/DECLARE @emptybody2 VARCHAR(MAX) SET @emptybody2='' SET @emptybody2 = '<table cellpadding="5" cellspacing="5" border="0">' + '<tr> <th width="500"> </th> </tr>' + REPLACE( REPLACE( ISNULL(@emptybody2,''), '<', '<' ), '>', '>' ) + '</table>' /************************************************************** Sending Email***************************************************************/ DECLARE @subject AS VARCHAR(500) DECLARE @importance AS VARCHAR(6) DECLARE @EmailBody VARCHAR(MAX)SET @importance ='High' DECLARE @recipientsList VARCHAR(8000)SELECT @recipientsList = 'Dba@PracticalSqlDba.com;nelsonaloor@PracticalSqlDba.com'
SET @subject = 'Backup Report of MYSql Instance' SELECT @EmailBody =@FullBackupHeader+@emptybody2+@FullBackupTable+@emptybody2+@DiffBackupHeader +@emptybody2+@DiffBackupTableEXEC msdb.dbo.sp_send_dbmail @profile_name ='MyMailProfile', @recipients=@recipientsList,@subject = @subject , @body = @EmailBody , @body_format = 'HTML' , @importance=@importance
You can download the formatted script from here. I have implemented this method to send various statistical/alert mail. Hope this will help you.
If you liked this post, do like my page on FaceBook
Loading comments...