Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

SELECT  
   distinct CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
 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'
ORDER BY  
   msdb.dbo.backupset.database_name, 
   msdb.dbo.backupset.backup_start_date

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?

share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

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';

SELECT  
   bs.database_name,  
   bs.backup_start_date,  
   bs.backup_finish_date, 
   [Total Time] = CAST((DATEDIFF(SECOND, bs.backup_start_date,bs.backup_finish_date))
     AS VARCHAR(30))+ ' secs',
   CAST(bs.backup_size/1024/1024 AS NUMERIC(10,2)) AS 'Backup Size(MB)',   
   h.[message]
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
AND ABS(DATEDIFF(SECOND, bs.backup_start_date, CONVERT(DATETIME,CONVERT(CHAR(8), h.run_date) 
  + ' ' + STUFF(STUFF(RIGHT('0'+CONVERT(VARCHAR(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);

SELECT 
   @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'
  END
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 UPPER(CONVERT(NVARCHAR(MAX),dt.TextData)) LIKE N'BACKUP%DATABASE%'
--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.

share|improve this answer
    
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 at 18:52
    
@iamZel then you are on SQL Server 2005, not SQL Server 2008. –  Aaron Bertrand Jan 30 at 18:54
    
Yes, I am. I forgot to mention that. I'm on SQL2K5 –  iamZel Jan 30 at 19:51
    
@iamZel The reason I thought you were on SQL Server 2008 is because you tagged your question with that version. Please tag carefully. –  Aaron Bertrand Jan 30 at 19:52
    
Yes, you're right. My mistake., –  iamZel Jan 30 at 19:57
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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