I have two jobs that backs up two different databases.
Job 1 backs up DB1
Job 2 backs up DB2

DB1 fails to back up due to low space on Drive 1 resulting to a Job 1 failure. To fix that issue, I just had to add space. No biggie. I was told about this today when the issue has been occuring for a month . Yeah I know it's crazy but it's dev

I want to get a complete backup history for DB1. I know that I can retrieve successful backup information from the msdb.dbo.backupset table but I want to know if there is a query that displays failed backups for a database.

My query below displays backup history for a particular database from 12/31/13-1/27/14. Info includes server, database name, Backup start and end times, Total time it took for the dbs to be backed up, db size and backup set name.

   distinct CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
 CAST((DATEDIFF(second,  msdb.dbo.backupset.backup_start_date,msdb.dbo.backupset.backup_finish_date)) AS varchar)+ ' secs  ' AS [Total Time] ,

   Cast(msdb.dbo.backupset.backup_size/1024/1024 AS numeric(10,2)) AS 'Backup Size(MB)',   
   msdb.dbo.backupset.name AS backupset_name
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id   
--Enter your database below
--and database_name = 'db_name_here'
and msdb.dbo.backupset.backup_start_date>'2013-12-31' and msdb.dbo.backupset.backup_start_date<'2014-01-27 23:59:59'

Is there any way to get that information by modifying my code? I'm able to retrieve JOB1's history by executing a sql statement that runs against the sysjobhistory and sysjob table. This may be a long shot Is there a way I can use the sysjobhistory, sysjob, backupset and backupsetmediafamily tables in msdb to produce the results I want?

1 Answer 1


Sadly, backupset does not contain failed backups, and I don't know of anywhere else in msdb these may be stored, unless you can rely on sysjobhistory, which doesn't contain all of time (depending on your retention settings), and which would ignore any backup attempts that were made outside the context of a job, and which - in the case of a job that backs up many databases - would not provide differentiation about which database actually failed, unless it happened to happen early on in the job - this is because the messaging is quite verbose but gets truncated.

If you absolutely know that Job n only backs up the one database, and that every failure of that job means that the database wasn't backed up (since the job could also fail after the backup succeeded, e.g. trying to shrink or perform other maintenance), then you could use a query like this:

DECLARE @job sysname, @db sysname;

SELECT @job = N'Job 1', @db = N'db_name';

   [Total Time] = CAST((DATEDIFF(SECOND, bs.backup_start_date,bs.backup_finish_date))
     AS varchar(30))+ ' secs',
   CAST(bs.backup_size/1024/1024 AS decimal(10,2)) AS 'Backup Size(MB)',   
FROM msdb.dbo.sysjobhistory AS h
INNER JOIN msdb.dbo.sysjobs AS j
ON h.job_id = j.job_id
AND h.step_id = 0
LEFT OUTER JOIN msdb.dbo.backupset AS bs
ON bs.database_name = @db
 ABS(DATEDIFF(SECOND, bs.backup_start_date, CONVERT(DATETIME,convert(char(8),h.run_date) 
   + ' ' + STUFF(STUFF(RIGHT('0'+CONVERT(char(6),h.run_time),6),3,0,':'),6,0,':')))) < 5
WHERE j.name = @job
ORDER BY bs.backup_start_date;

Yes, it's really ugly, because sysjobhistory still, in SQL Server 2014 even, stores run_date and run_time as separate integers. I bet whoever made that decision is still on the background of dartboards all over building 35. It also assumes that the backup is the very first step in the job, hence the rather less than scientific date/time comparison to make sure we've properly correlated the right instance of the job to the right instance of the backup. Oh, how I wish I could redesign the schema for backups and jobs.

If you want broader scope outside of the job, you can look for failed backups in the SQL Server error log (if they haven't been cycled away):

EXEC sp_readerrorlog 0, 1, 'BACKUP failed'; -- current
EXEC sp_readerrorlog 1, 1, 'BACKUP failed'; -- .1 (previous)
EXEC sp_readerrorlog 2, 1, 'BACKUP failed'; -- .2 (the one before that)

(But I don't know of a nice and easy way to incorporate that output into your existing query.)

You can also correlate "missing" successful backups from the default trace, e.g.

DECLARE @path nvarchar(260);

   @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT dt.DatabaseName, dt.StartTime, bs.backup_start_date, bs.backup_finish_date, 
  [Status] = CASE WHEN bs.backup_start_date IS NULL 
    THEN 'Probably failed'
    ELSE 'Seems like success'
FROM sys.fn_trace_gettable(@path, DEFAULT) AS dt
LEFT OUTER JOIN msdb.dbo.backupset AS bs
ON dt.DatabaseName = bs.database_name
AND ABS(DATEDIFF(SECOND, dt.StartTime, bs.backup_start_date)) < 5
WHERE dt.EventClass = 115 -- backup/restore events
--AND dt.DatabaseName = N'db_name' -- to filter to a single database
--AND bs.database_name = N'db_name'
ORDER BY dt.StartTime;

Of course this also relies on the data from the default trace cycling away, the database name not having changed, etc. And unfortunately, the default trace doesn't differentiate between successful and failed backups, and the start time will not precisely match the MSDB data, but as long as you're not running backups in a loop, this should be okay for eyeballing. I've tried to incorporate these issues into the query.

Finally, you may want to use a FULL OUTER JOIN there, in case backupset has longer history than the default trace. This changes the semantics of [Status] slightly.

You also might want to give this nasty thing a try, though I didn't have much luck with it. I was only able to see the current or most recent status, so that only helped when the job failed the last time it ran, and - like sysjobhistory - wasn't able to obtain information about any backups that were attempted but not through a job.

  • Thank you very much for the detailed explanation but I receive an error when I execute the first query. Msg 139, Level 15, State 1, Line 0 Cannot assign a default value to a local variable. Msg 137, Level 15, State 2, Line 16 Must declare the scalar variable "@db"
    – iamZel
    Jan 30, 2014 at 18:52
  • @iamZel then you are on SQL Server 2005, not SQL Server 2008. Jan 30, 2014 at 18:54
  • Yes, I am. I forgot to mention that. I'm on SQL2K5
    – iamZel
    Jan 30, 2014 at 19:51
  • 1
    @iamZel The reason I thought you were on SQL Server 2008 is because you tagged your question with that version. Please tag carefully. Jan 30, 2014 at 19:52
  • sp_readerrorlog is good enough for me. Thanks a lot Aaron
    – iamZel
    Feb 3, 2014 at 17:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.