Script to find failed SQL Server Backups and validate re-running code was successful

By:   |   Updated: 2017-11-01   |   Comments   |   Related: More > SQL Server Agent

Problem

We have notifications configured to let us know when a backup job fails. However, that doesn’t tell me if we successfully re-ran the backup job and now have a good backup. As a result, trying to determine what databases have outstanding failed backups with no later successful backups is a problem. Is there an easy way to do this?

Solution

If you’re performing SQL Server native backups via SQL Server Agent, there are two tables in msdb that you can query to get information on job runs:

  • sysjobs
  • sysjobhistory

Determining which jobs belong to backups can be difficult if we’re relying strictly on the job name, especially if there are issues with consistency in the names. The easiest way to deal with this is to ensure we create a job category specifically for backups.

Here’s how you go about it if you’re using the SQL Server Management Studio. First, right click on the Jobs folder under SQL Server Agent. Then select Manage Job Categories:

Manage Job Categories from SSMS

Next, choose to add a category. Type in the name you want to use, such as Backups, and click OK. When you go to create your backup jobs (or if you have existing backup jobs already), make sure they use that category.

Add Backups to Job Categories list

Alternately, you could simply add the category using a script. I do this for all my categories when I build a new SQL Server. It ensures consistency:

IF NOT EXISTS(SELECT name FROM msdb.dbo.syscategories WHERE name = 'Backups') 
EXEC msdb.dbo.sp_add_category @name = 'Backups'; 

With that new category, we need to add another table: syscategories.

This gets us to the following query:

SELECT j.job_id, j.name 
FROM msdb.dbo.sysjobs j
  JOIN msdb.dbo.syscategories c
    ON j.category_id = c.category_id
  JOIN (SELECT h1.job_id, h1.run_status
FROM msdb.dbo.sysjobhistory AS h1
  JOIN (SELECT job_id, MAX(instance_id) AS instance_id
FROM msdb.dbo.sysjobhistory
WHERE step_id = 0
GROUP BY job_id) AS h2
ON h1.job_id = h2.job_id AND h1.instance_id = h2.instance_id) AS h
    ON j.job_id = h.job_id
WHERE j.[enabled] = 1
  AND h.run_status = 0
  AND c.[name] = 'Backups'; 

Why This Works

Let’s step through the WHERE clause first. We’re filtering on three things:

  1. Enabled jobs (j.[enabled] = 1)
  2. Jobs that have a failed status (h.run_status = 0)
  3. Jobs in the Backups category (c.[name] = ‘Backups’)

Once we’ve filtered to that level, we need to be able to identify what was the last attempt to run a particular job. That’s the clause we call h2 in the query above. We’re getting step_id = 0 because that’s the overall result of the job. We can’t retrieve the run_status yet. We need the absolute last result. If we include run_status at this point, and we have multiple run_status values, say one for failed and one for completed successfully, we’ll get two rows because of the way GROUP BY works. Therefore, we get the max, meaning the last execution of the job.

Have each job and its last execution, we join back to sysjobhistory and retrieve the run_status at this point. Then we join to syscategories in order to filter to just backups. Then we apply the filter and what we should have left are the backup jobs which failed the last time they ran:

Results of Failed Backup Query

What This Doesn’t Handle

This method of finding failed backups only encompasses looking at SQL Server Agent. If someone runs a backup outside of the SQL Server Agent Jobs, you won’t have all the information. For instance, if someone gets the failed job notification and then manually backs up the database, say by right-clicking on it in SSMS, then we won’t have this information reflected in the job history. Similarly, if someone ran a backup manually outside of the jobs and that attempt failed, we won’t know. There are other scenarios where backups could be attempted. Anything outside of the SQL Server Agent Job execution won’t be captured. You will have to determine some other way of reconciling the information.

Next Steps


Last Updated: 2017-11-01


get scripts

next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools