Sometimes we all miss our failed job alerts it can lead to trouble though when you need to maintain compliance.
Here is a proc which will let you know if a database has not been backed up in more then X days.
CREATE PROCEDURE [dbo].[proc_CheckLastBackupDate]
@daystoCheck int,
@recipients varchar(255),
@profile varchar(255)
AS
BEGIN
SET
NOCOUNT
ON
BEGIN
TRY
/*Find out if a database has not been backed up for more then X days */
DECLARE @Database TABLE (DBName varchar(255), backupDiff int)
INSERTINTO @DatabaseSELECT A.name,datediff(dd,B.LastBackupDate,GETDATE()) DayDiffFROMsys.databases A
INNERJOIN(SELECT a.database_name,max(convert(date,a.backup_finish_date )) LastBackupDateFROM msdb..backupset aINNERJOINmaster.sys.databases b on a.database_name = b.nameWHERE a.type='D'GROUPBY database_name)
B
on A.name = B.database_nameWHEREdatediff(dd,B.LastBackupDate,GETDATE())> @daystoCheck
IF (@@ROWCOUNT> 1)BEGIN
DECLARE @tableHTML NVARCHAR(MAX), @currentDate date, @usagepercentage int
SET @tableHTML =N'<Table border =1 cellpadding=5>'+N'<tr><th>DBname</th>'+N'<th>Last Backup</th>'+N'</tr>'+CAST(
( SELECT
td
=convert(varchar(30),database_name),'',
td
=convert(varchar(60),LastBackupDate),''
FROM (select database_name,max(a.backup_finish_date) LastBackupDatefrom msdb..backupset aINNERJOIN @Database n on A.database_name = n.DBNamewhere a.type='D'GROUPBY database_name)a
FORXMLPATH('tr'),TYPE)asNVARCHAR(max))+N'</table>'
declare @subject varchar(255)SELECT @subject ='The Following Databases May not have been backed Up on '+@@servernameexec msdb.dbo.sp_send_dbmail
@profile_name = @profile,
--'sqladmin',
-- @recipients = 'Maryanne_frake@adp.com',
@recipients
= @recipients,
@subject
= @subject,
@body
= @tableHTML,
@body_format
='HTML'
END
END
TRY
BEGIN
CATCH
DECLARE @ErrorMessage varchar(max)
SELECT @ErrorMessage ='ErrorNumber:'+convert(varchar(7),ERROR_NUMBER())+'<br>'+'ErrorSeverity:'+convert(varchar(7),ERROR_SEVERITY())+'<br>'+'ErrorState:'+convert(varchar(7),ERROR_STATE())+'<br>'+'Calling Procedure:'+CONVERT(VARCHAR(25),OBJECT_NAME(@@PROCID))+'<br>'+'ErrorProcedure:'+ERROR_PROCEDURE()+'<br>'+'ErrorLine:'+convert(varchar(7),ERROR_LINE())+'<br>'+'ErrorMessage:'+ERROR_MESSAGE()+'<br><hr>'RAISERROR(@ErrorMessage,16,1)
END
CATCH
END
GO
Posted by motherofallgeeks 
