Having Problem in restoring transaction log backup

  • Hi,

    While restoring a database, which was damaged due to hardware failure. I Got the following Error Message,

    "The log in this backup set cannot be applied because it is on a recovery path inconsistent with the database RESTORE LOG is terminating abnormally"

    Let me describe the Backup plan and effects after hardware failure.

    Backup plan

    -------------

    Database Recovery Model : - FULL

    Once in a month Complete Backup

    Every day when sql server agent starts Differential Backup.

    Transaction Log Starts 10.00 am and repeats every 20 minutes.

    Hardware Failure.

    -----------------

    No problem with MDF File but CRC check failure for LDF file, The Database went suspect when sql server started. I ran DBCC cehckdb with 0 consistency errors.

    I started restoring the database, I could restore the database from Last Complete backup to last Differential backup . While restoring the transactional log I got the above mentioned error.

    Since the database is very crucial I need Help to restore these Transactional log backups.

    Thanks in advance

  • Are you sure it's the correct log backup?

    How are you doing the restores?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • The error is stating that you are not restoring the correct transaction log backup. After restoring your differential backup, you can only restore transaction log backups that follow that differential backup.

    This is just a guess, but are you using devices (single file) to contain your transaction log backups? If so, when (and how often) do you initialize that file? Are you using devices for your full and differentials?

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I agree with Gail. It sounds like the log backups are from a different database than the full/differential backups. If you look at the logical filenames using RESTORE FILELISTONLY, does it match the name of the database the full/differential backups point to?

    http://msdn.microsoft.com/en-us/library/ms173778%28SQL.90%29.aspx

    Joie Andrew
    "Since 1982"

  • I am Sorry for replying late,

    It was my mistake, I was trying to restore the database after renaming Log file since CRC check failure.

    I deleted The database and created a new blank one with the same name. I could restore the database till last log back up.

    Thanks allot for your help.

  • This error will show up also if you try to restore a T-LOG backup that was done after a TRUNCATE_ONLY command. In other words if you follow this sequence:

    1. FULL BACKUP

    2. BACKUP LOG [DB_NAME] WITH TRUNCATE_ONLY

    3. TRANSACTION LOG BACKUP

    then your transaction log from the 3rd step is not restorable. This is possible on a SQL 2000 version only! SQL 2005 won't even allow you to do the 3rd step untill you do a full backup after your TRUNCATE_ONLY, to start a new LSN sequence.

  • Please note: 3 year old thread.

    The error in the OP is not one you get when the log chain has been broken. That case gets you an error either like 'Cannot restore because no current backup exists' or 'cannot restore because the log backup is too recent to apply'

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks Gail,

    If I repeat the same sequence of steps with a CHECKPOINT added either after step 1 (FULL) or after step 2 (BACKUP LOG WITH TRUNCATE_ONLY) then the restore attempt after step 3 (BACKUP LOG) produces:

    [font="Courier New"]'The log in this backup set begins at LSN ....2, which is too late to apply to the database. An earlier log backup that includes LSN ....1 can be restored.'[/font]

    Without the CHECKPOINT I am consistantly getting:

    [font="Courier New"]'The log in this backup set cannot be applied because it is on a recovery path inconsistent with the database.'[/font]

    Aren't both errors in effect caused by TRUNCATE_ONLY (which breaks the LSN, deosn't it) from step 2?

    I know it's an old subject but this post shows up high on google search when you look up the 'recovery path inconsistent' error, and there are quite a few shops out there still running 2000 versions, where previous admins tried to contain their runaway T-Log by nightly TRUNCATE_ONLY jobs. I just inherited one of those.

  • maintaining Log backup files is the most tricky part.

    if u missed the chain then, everything becomes problematic.

  • Marek Grzymala (9/23/2013)


    Aren't both errors in effect caused by TRUNCATE_ONLY (which breaks the LSN, deosn't it) from step 2?

    The first one is, but the second occurs when you've forked the restore chain at some point. Hence the 'inconsistent paths'

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 10 posts - 1 through 9 (of 9 total)

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