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
