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' ;