4 Replies Latest reply on Jun 6, 2016 8:33 PM by gcp

    DPA Backup SQL Server

    pnogueira

      Hi All,

      How can I create a DPA alert for failed backup executed in SQL Server ? Do you guys have the SQL to insert into the CUSTOM option? I did not find a Pre defined alert ?

        • Re: DPA Backup SQL Server
          gcp

          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.

            • Re: DPA Backup SQL Server
              jaminsql

              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.

              • Re: DPA Backup SQL Server
                pnogueira

                Another doubt. It's possible to get a result using a "Completed" or "Failed".

                  • Re: DPA Backup SQL Server
                    gcp

                    @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.