Thursday 23 February 2012

Backup database report in the server

Add the below script to step of backup job to get an alert

DECLARE @tableHTML  NVARCHAR(MAX) ;
SET @tableHTML =
    N'<H2>Last Backup Report For</H2>' +
    N'<H3>'+ (select td = (select @@servername)) +'</H3>'+
    N'<table border="1">' +
    N'<tr><th>Database Name</th><th>Days Since Last Backup</th>' +  
    N'<th>Last Backup Date</th>' +
    CAST ( ( SELECT td = msdb.dbo.backupset.database_name,       '',
                    td = datediff(day, max(msdb.dbo.backupset.backup_finish_date), getdate()), '',
                    td = CONVERT(VARCHAR(20), max(msdb.dbo.backupset.backup_finish_date), 100)
              FROM msdb.dbo.backupmediafamily
           INNER JOIN msdb.dbo.backupset
        ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
              WHERE msdb.dbo.backupset.database_name IN (SELECT name FROM master.sys.databases)
       GROUP BY msdb.dbo.backupset.database_name
       ORDER BY datediff(day, max(msdb.dbo.backupset.backup_finish_date), getdate()) DESC
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'Project',
    @recipients='Email Id',
    @subject = ' Production Server Backup Report',
    @body = @tableHTML,
    @body_format = 'HTML' ;