How to Query msdb for Failed Backups

  • We learned today that our backups were not working. Fortunately, we didn't have any database failures along the way.

    We have a "locked down" shop and do not have access to the backups themselves, or any logs created when they ran/failed. Is there a way to query msdb to determine if a backup fails? I tried the following, but I don't see anything that would indicate a failure.

    use msdb

    select

    *

    from

    backupset

    where

    database_name = 'camp'

    order by

    backup_start_date desc

  • I can't think of a way to do that directly, but you could certainly query that table and compare it to a calendar of when backups are supposed to be run, and find any missing dates. Right?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • If the job is failing, you should configure it to send you an email notification.

    I think failed jobs would be in job history, not backup history.

    To double check your current situation, you can run something like this:

    -- Databases NOT Backed Up Since YYYY-MM-DD

    select * from master..sysdatabases db

    where (not exists

    (select * from msdb.dbo.backupset

    where db.name = msdb.dbo.backupset.database_name

    and backup_start_date > '2009-11-15'))

    order by name

    Or this to see when the most recent FULL, DIFF or LOG backup was for each database.

    -- Most Recent Backups and # of days since ANY type of backup

    SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,

    ISNULL(Convert(char(19), MAX(backup_finish_date), 100), 'NEVER') as LastBackupDate,

    case

    when type='D' then '** FULL **'

    when type='I' then 'DIFFERENTIAL'

    when type='L' then 'LOG'

    end as Backup_Type,

    case

    when status > 16 then 'Check DB Status' -- Alert that DB might be ReadOnly, Offline etc...

    else ' '

    end as 'DB Status'

    FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name --AND A.type = 'D'

    where B.name not like '%skip these%'

    GROUP BY B.name , a.type, status

    ORDER BY B.name , LastBackupDate desc,a.type, status

    Here's some code I had for finding failed jobs:

    -- Find any failed job for today's date

    SELECT 'Job: ' + Job.[name], Hst.[sql_message_id], Hst.[message] , Hst.[run_date], Hst.[run_time],'Hist', hst.*

    FROM [msdb].dbo.sysjobhistory Hst

    INNER JOIN [msdb].dbo.sysjobs Job ON Hst.[job_id] = Job.[job_id]

    where hst.run_status = '0' -- 0 = FAILED

    and convert(varchar(8), GETDATE(),112)= Hst.[run_date]

    ORDER BY Job.[name],Hst.[run_date] DESC, Hst.[run_time] DESC

    They're all old 2000 code but will work in 2005.

  • homebrew01 (11/16/2009)


    If the job is failing, you should configure it to send you an email notification.

    In a perfect world, I would agree with you on this point. However, our site has very unreliable email. Just because a job sends an email failure message, does not necessarily mean that it will be delivered in a timely manner, and it may not get delivered at all... Sigh.

    Thanks for the query suggestions. I'll check them out.

  • Or this to see when the most recent FULL, DIFF or LOG backup was for each database.

    .

    .

    .

    Here's some code I had for finding failed jobs:

    .

    .

    .

    Thanks, this helps a lot!

  • This may help you...

    /**** Below query can be used to directly pull the failed job on a

    particular day and other relevant information. It can be modified to

    match the exact requirement ****/

    SELECT sj.[name],

    sh.step_id,

    sh.step_name,

    sh.run_date,

    sh.run_time,

    sh.sql_severity,

    sh.message,

    sh.server

    FROM msdb.dbo.sysjobs sj

    INNER JOIN msdb.dbo.sysjobhistory sh

    ON sh.job_id = sj.job_id

    INNER JOIN msdb.dbo.sysjobsteps ss

    ON sj.job_id = ss.job_id

    AND sh.step_id = ss.step_id

    WHERE sh.run_status = 0 /*Failure-0, Success-1*/

    AND sh.run_date > 'Provide Date here'

    ORDER BY sh.instance_id ASC

    Source : http://technotes.towardsjob.com/sql-server/troubleshooting-sql-server-2005-jobs-failure/

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply