TSQL to check database backup status

  • Hello,

    I am trying to generate a monthly report for sql database backup job. Is there a script that I can use to query msdb to check the backup success or failure status of a specific database?

    Thanks for your inputs

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

  • You can use the following script to query the Error Log and get result for all Database backups and restores:

    Declare @ArchiveNum Int

    Declare @Arch_Temp Table(ArchiveNo Int, Date DateTime, LogFileSize BigInt)

    Declare @Err_temp Table(LogDate DateTime, ProcessInfo NVarchar(30), Text Nvarchar(MAX))

    Insert Into @Arch_Temp

    Execute xp_enumerrorlogs

    Declare Arch_Cursor Cursor LOCAL STATIC FORWARD_ONLY

    For

    Select ArchiveNo From @Arch_Temp

    Open Arch_Cursor

    Fetch NEXT From Arch_Cursor Into @ArchiveNum

    While(@@FETCH_STATUS = 0)

    Begin

    Insert Into @Err_temp

    EXEC sys.xp_readerrorlog @ArchiveNum

    Fetch NEXT From Arch_Cursor Into @ArchiveNum

    End

    Close Arch_Cursor

    DeAllocate Arch_Cursor

    Select * From @Err_temp Where ProcessInfo = 'Backup'

    You can alter the Script according to your requirement. Hope this is close to what you are looking for.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • Thanks for the script

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

  • if your just looking for a report of backups you can query msdb.dbo.backupset which contains allot of information

    select top 10 b.database_name, b.backup_start_date, b.backup_finish_date

    from msdb.dbo.backupset b

    with a join to sys.databases you can eliminate system databases if you dont care about them or can get more detailed info about the databases. no need to run through the error log with a cursor.


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • capnhector (11/7/2012)


    if your just looking for a report of backups you can query msdb.dbo.backupset which contains allot of information

    select top 10 b.database_name, b.backup_start_date, b.backup_finish_date

    from msdb.dbo.backupset b

    with a join to sys.databases you can eliminate system databases if you dont care about them or can get more detailed info about the databases. no need to run through the error log with a cursor.

    I was able to pull that information already. But wanted to identify the status of a database backup (failed/succeeded) which doesnt get logged in msdb.

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

  • sapen (11/7/2012)


    Thanks for the script

    You're Welcome Sapen. 🙂

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • SELECT r.session_id AS [Session_Id]
      ,r.command AS [command]
      ,CONVERT(NUMERIC(6, 2), r.percent_complete) AS [% Complete]
      ,GETDATE() AS [Current Time]
      ,CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GetDate()), 20) AS [Estimated Completion Time]
      ,CONVERT(NUMERIC(32, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min]
      ,CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [Estimated Min]
      ,CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [Estimated Hours]
      ,CONVERT(VARCHAR(1000), (
        SELECT SUBSTRING(TEXT, r.statement_start_offset / 2, CASE
            WHEN r.statement_end_offset = - 1
              THEN 1000
            ELSE (r.statement_end_offset - r.statement_start_offset) / 2
            END) 'Statement text'
        FROM sys.dm_exec_sql_text(sql_handle)
        ))
    FROM sys.dm_exec_requests r
    WHERE command like 'RESTORE%'
    or command like 'BACKUP%'

  • You can directly search for "Backup" "failed" messages without having to pull the entire log file first, viz:

    EXEC sys.xp_readerrorlog @ArchiveID, 1, N'Backup', N'failed'

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 8 posts - 1 through 7 (of 7 total)

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