Backup failure History....

  • Hi Team,

    I need to find out the backup failure history.....

    Till date how many backup are failed in sql server how can I find that...

    please help...

    Sagar Sonawane
    ** Every DBA has his day!!:cool:

  • Saga... (12/28/2011)


    Hi Team,

    I need to find out the backup failure history.....

    Till date how many backup are failed in sql server how can I find that...

    please help...

    Could be wrong here but if I get what you're after your backup should be hopefully setup as a maintaince task/SQL Agent Job.

    If you right click on the task itself and select "View History" it will give you the information you require i.e. Date, Plan Name,Duration, Error Number & Error Message (plus more). You can then investigate that error message in full.

    Hopefully that's what you were trying to get as an answer - if not expand more.

  • Thanks TAVA for reply.

    Actually I was seeking information for the failed backups in SQL Server. (Those backups which are not successful. We can notify it in error log, job history. Is there any other way??)

    Sagar Sonawane
    ** Every DBA has his day!!:cool:

  • Saga... (12/28/2011)


    Thanks TAVA for reply.

    Actually I was seeking information for the failed backups in SQL Server. (Those backups which are not successful. We can notify it in error log, job history. Is there any other way??)

    I'm not to sure other than Job History, As i have never needed to look at other alternatives. You can have alerts created if you're using "Maintenance Plans" to notify you upon an unsuccessful backup but this would be for new backups not previously failed ones priori.

  • Tava (12/28/2011)


    Saga... (12/28/2011)


    Thanks TAVA for reply.

    Actually I was seeking information for the failed backups in SQL Server. (Those backups which are not successful. We can notify it in error log, job history. Is there any other way??)

    I'm not to sure other than Job History, As i have never needed to look at other alternatives. You can have alerts created if you're using "Maintenance Plans" to notify you upon an unsuccessful backup but this would be for new backups not previously failed ones priori.

    Just to add, not sure if this covers Unsuccessful backups or not but saw this article on MSDN website. http://msdn.microsoft.com/en-us/library/ms188653.aspx "A complete history of all SQL Server backup and restore operations on a server instance is stored in the msdb database"

  • Use the query here to find your failed jobs.

    http://sqlserverpedia.com/wiki/SQL_Server_Agent_Job_Query_Samples

    To query backups for a database, you can query the backupset (and backup tables) in the msdb database.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • As I found query for backup history from msdb..backupset

    select database_name,backup_start_date,backup_finish_date,type, backup_size from msdb.dbo.backupset WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 30)

    ORDER BY

    msdb.dbo.backupset.database_name,

    msdb.dbo.backupset.backup_finish_date

    I didn't found any query which will retrieve failed backup information...

    Sagar Sonawane
    ** Every DBA has his day!!:cool:

  • Hopefully you will have the backups scheduled through an agent job. If you do, you can report on job history to find if a backup job completed successfully or not.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • I have scheduled Jobs for SQL backups. for daily, weekly and monthly.

    Found last months backup output as 54217 rows.

    manually its not possible to check job history.

    Can you suggest any query for failed jobs. (query provided in http://sqlserverpedia.com/wiki/SQL_Server_Agent_Job_Query_Samples#Query_Test_Checklist)

    is very lengthy... will make overhead on server.

    Sagar Sonawane
    ** Every DBA has his day!!:cool:

  • This query is fairly quick. If you need to know the success/failure of your jobs and you have that much history to comb, you will need to run a query such as this.

    SET NOCOUNT ON

    DECLARE @MaxLength INT

    SET @MaxLength = 50

    DECLARE @xp_results TABLE (

    job_id uniqueidentifier NOT NULL,

    last_run_date nvarchar (20) NOT NULL,

    last_run_time nvarchar (20) NOT NULL,

    next_run_date nvarchar (20) NOT NULL,

    next_run_time nvarchar (20) NOT NULL,

    next_run_schedule_id INT NOT NULL,

    requested_to_run INT NOT NULL,

    request_source INT NOT NULL,

    request_source_id sysname

    COLLATE database_default NULL,

    running INT NOT NULL,

    current_step INT NOT NULL,

    current_retry_attempt INT NOT NULL,

    job_state INT NOT NULL

    )

    DECLARE @job_owner sysname

    DECLARE @is_sysadmin INT

    SET @is_sysadmin = isnull (is_srvrolemember ('sysadmin'), 0)

    SET @job_owner = suser_sname ()

    INSERT INTO @xp_results

    EXECUTE sys.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

    UPDATE @xp_results

    SET last_run_time = right ('000000' + last_run_time, 6),

    next_run_time = right ('000000' + next_run_time, 6)

    SELECT j.name AS JobName,

    j.enabled AS Enabled,

    CASE x.running

    WHEN 1

    THEN

    'Running'

    ELSE

    CASE h.run_status

    WHEN 2 THEN 'Inactive'

    WHEN 4 THEN 'Inactive'

    ELSE 'Completed'

    END

    END

    AS CurrentStatus,

    coalesce (x.current_step, 0) AS CurrentStepNbr,

    CASE

    WHEN x.last_run_date > 0

    THEN

    convert (datetime,

    substring (x.last_run_date, 1, 4)

    + '-'

    + substring (x.last_run_date, 5, 2)

    + '-'

    + substring (x.last_run_date, 7, 2)

    + ' '

    + substring (x.last_run_time, 1, 2)

    + ':'

    + substring (x.last_run_time, 3, 2)

    + ':'

    + substring (x.last_run_time, 5, 2)

    + '.000',

    121

    )

    ELSE

    NULL

    END

    AS LastRunTime,

    CASE h.run_status

    WHEN 0 THEN 'Fail'

    WHEN 1 THEN 'Success'

    WHEN 2 THEN 'Retry'

    WHEN 3 THEN 'Cancel'

    WHEN 4 THEN 'In progress'

    END

    AS LastRunOutcome,

    CASE

    WHEN h.run_duration > 0

    THEN

    (h.run_duration / 1000000) * (3600 * 24)

    + (h.run_duration / 10000 % 100) * 3600

    + (h.run_duration / 100 % 100) * 60

    + (h.run_duration % 100)

    ELSE

    NULL

    END

    AS LastRunDuration

    FROM @xp_results x

    LEFT JOIN

    msdb.dbo.sysjobs j

    ON x.job_id = j.job_id

    LEFT OUTER JOIN

    msdb.dbo.syscategories c

    ON j.category_id = c.category_id

    LEFT OUTER JOIN

    msdb.dbo.sysjobhistory h

    ON x.job_id = h.job_id

    AND x.last_run_date = h.run_date

    AND x.last_run_time = h.run_time

    AND h.step_id = 0

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • You can check out from here also

    « SQL SERVER – Recycle Error Log – Create New Log file without Server RestartSQLAuthority News – SQL Server Denali CTP1 – Release Date November 9, 2010 »

    SQL SERVER – Get Database Backup History for a Single Database

    November 10, 2010 by pinaldave

    I recently wrote article SQL SERVER – Finding Last Backup Time for All Database and requested blog readers to respond with their own script which they use it Database Backup.

    Here is the script suggested by SQL Expert aasim abdullah, who has written excellent script which goes back and retrieves the history of any single database.

    USE AdventureWorks

    GO

    -- Get Backup History for required database

    SELECT TOP 100

    s.database_name,

    m.physical_device_name,

    CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,

    CAST(DATEDIFF(second, s.backup_start_date,

    s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,

    s.backup_start_date,

    CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,

    CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,

    CASE s.[type]

    WHEN 'D' THEN 'Full'

    WHEN 'I' THEN 'Differential'

    WHEN 'L' THEN 'Transaction Log'

    END AS BackupType,

    s.server_name,

    s.recovery_model

    FROM msdb.dbo.backupset s

    INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id

    WHERE s.database_name = DB_NAME() -- Remove this line for all the database

    ORDER BY backup_start_date DESC, backup_finish_date

    GO

    http://blog.sqlauthority.com/2010/11/10/sql-server-get-database-backup-history-for-a-single-database/

  • logicinside22 (12/30/2011)


    You can check out from here also

    « SQL SERVER – Recycle Error Log – Create New Log file without Server RestartSQLAuthority News – SQL Server Denali CTP1 – Release Date November 9, 2010 »

    SQL SERVER – Get Database Backup History for a Single Database

    November 10, 2010 by pinaldave

    I recently wrote article SQL SERVER – Finding Last Backup Time for All Database and requested blog readers to respond with their own script which they use it Database Backup.

    Here is the script suggested by SQL Expert aasim abdullah, who has written excellent script which goes back and retrieves the history of any single database.

    USE AdventureWorks

    GO

    -- Get Backup History for required database

    SELECT TOP 100

    s.database_name,

    m.physical_device_name,

    CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,

    CAST(DATEDIFF(second, s.backup_start_date,

    s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,

    s.backup_start_date,

    CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,

    CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,

    CASE s.[type]

    WHEN 'D' THEN 'Full'

    WHEN 'I' THEN 'Differential'

    WHEN 'L' THEN 'Transaction Log'

    END AS BackupType,

    s.server_name,

    s.recovery_model

    FROM msdb.dbo.backupset s

    INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id

    WHERE s.database_name = DB_NAME() -- Remove this line for all the database

    ORDER BY backup_start_date DESC, backup_finish_date

    GO

    http://blog.sqlauthority.com/2010/11/10/sql-server-get-database-backup-history-for-a-single-database/%5B/quote%5D

    This is ok if you are using a native sql backup - We use Comvault and this still populates the backup_finish_date even if the job fails 🙁

    UPDATE - Scrub that - Comvault records the successful re-try but does not log the initial failure in msdb.dbo.backupset

    You could also use Exec msdb.dbo.sp_help_jobhistory @run_status = 0 to get a quick list of all failed jobs

    SQL DBA
    Every day is a school day, and don't trust anyone who tells you any different.
    http://sqlblogness.blogspot.co.uk

  • SELECT sysjobhistory.server,sysjobs.name AS job_name,

    CASE sysjobhistory.run_status

    WHEN 0 THEN 'Failed'

    WHEN 1 THEN 'Succeeded'

    END AS run_status

    FROM msdb.dbo.sysjobhistory

    INNER JOIN msdb.dbo.sysjobs

    ON msdb.dbo.sysjobhistory.job_id = msdb.dbo.sysjobs.job_id

    Where sysjobs.name = 'YourBackupJobName'

  • Hi,

    Is the script applicable on SQL Server 2000, 2005 ?

    Thanks

    dev1

  • dev1.bohol (5/27/2013)


    Hi,

    Is the script applicable on SQL Server 2000, 2005 ?

    Thanks

    dev1

    Which script are you referring to?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 15 posts - 1 through 14 (of 14 total)

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