DPA Backup SQL Server

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.

Nice solution gcp​. I like the idea of the database name  There is one here  also that just alerts if there is not a full backup in the last day. Custom Alert - SQL Server Backup Monitoring it isn't giving the name however so you would end up having to look for that.

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:

Checking the status of SQL Server Agent jobs - SQLMatters

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.