You can set up a custom alert (Custom SQL Alert - Multiple Numeric Return) with something like this:
SELECT T1.NAME AS DatabaseName, CAST(COALESCE(DATEDIFF(second, MAX(T2.backup_finish_date) , CURRENT_TIMESTAMP)/ 3600.0, 9999.0) as DECIMAL (5,2)) AS [Hours Since Backup]
FROM master.sys.databases T1
LEFT OUTER JOIN msdb.dbo.backupset T2 ON T2.database_name = T1.NAME
WHERE T1.NAME NOT IN ('tempdb')
GROUP BY T1.NAME
ORDER BY T1.NAME
It returns the DB name and a decimal number representing hours since the last successful backup. Be aware that the alert levels are integer values, so you can't set an alert for 7.5 hours - 7 or 8 only
if your backups are run via SQL Agent, you can set up an Administrative alert for SQL Server Job Failures - but that may catch other things as well.
Another doubt. It's possible to get a result using a "Completed" or "Failed".
@pnogueira if you want to know the explicit status of the job, you'll need to look at msdb sysjobs and sysjobshistory and create your own custom alert. This may get you started:
The problem with using the custom alerts is that there are a limited number of alert formats you can use, and there is no easy way to get a custom alert for "Completed" or "Failed" - you may have to settle for TRUE/FALSE or NORMAL/HIGH which is not necessarily an equivalent method.
Hence why I suggested hours since last successful backup. If you run your backups daily (or on a regular schedule etc.) then knowing that there is no backup in x hours (or days - where x is large enough to cater for your backup cycle + some padding time) basically tells you that the job has failed to complete (could be still running, so neither "Completed" nor "Failed" ). I have had instances where the SQL agent job has either hung, or not actually started due to a problem with the SQL Agent service - even though the service appears to be active.