Backup Stauts

DECLARE @bodyMsg nvarchar(max)

DECLARE @subject nvarchar(max)

DECLARE @tableHTML nvarchar(max)

SELECT top (7) a.name, b.type, MAX(b.backup_finish_date) LastSuccessfulBackup,

CAST((GETDATE() - MAX(b.backup_finish_date)) AS NUMERIC(5, 2)) IntervalInDays

, c.physical_device_name,b.backup_finish_date

into #backupstatus

FROM master..sysdatabases a

LEFT OUTER JOIN msdb..backupset b

ON a.name = b.database_name

LEFT OUTER JOIN msdb..backupmediafamily c ON b.media_set_id = c.media_set_id

where  b.type = 'D'

GROUP BY a.name, b.type,c.physical_device_name,b.backup_finish_date

ORDER BY b.backup_finish_date DESC

SET @subject = 'Backup Status'

SET @tableHTML =

N''+   

N'

Backup Status

' +

N'' +

N' NAME

type

LastSuccessfulBackup

physical_device_name

backup_finish_date

' +

CAST ( (

SELECT td = CAST(NAME AS VARCHAR(100)),'',

td = CAST([type] AS VARCHAR(100)),'',

td = CAST([LastSuccessfulBackup] AS VARCHAR(100)),'',

td = CAST([physical_device_name] AS VARCHAR(100)),'',

td = CAST([backup_finish_date] AS VARCHAR(100))

FROM #backupstatus

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'

'

EXEC msdb.dbo.sp_send_dbmail  @recipients='moxsxsx@email.com,

@subject = @subject,

@body = @tableHTML,

@body_format = 'HTML' ;