Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Having Problem in restoring transaction log backup Expand / Collapse
Author
Message
Posted Saturday, January 2, 2010 5:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, January 31, 2010 7:52 AM
Points: 19, Visits: 43

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
Post #841143
Posted Saturday, January 2, 2010 5:21 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 39,910, Visits: 36,250
Are you sure it's the correct log backup?

How are you doing the restores?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #841144
Posted Saturday, January 2, 2010 9:40 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 4,358, Visits: 9,537
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
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #841162
Posted Sunday, January 3, 2010 2:12 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 4:02 AM
Points: 709, Visits: 1,422
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"
Post #841261
Posted Saturday, January 30, 2010 1:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, January 31, 2010 7:52 AM
Points: 19, Visits: 43
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.

Post #856591
Posted Thursday, September 19, 2013 2:19 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 8:02 AM
Points: 519, Visits: 566
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.
Post #1496259
Posted Thursday, September 19, 2013 4:21 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 39,910, Visits: 36,250
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 2008, MVP
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

Post #1496292
Posted Monday, September 23, 2013 2:34 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 8:02 AM
Points: 519, Visits: 566
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:

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

Without the CHECKPOINT I am consistantly getting:

'The log in this backup set cannot be applied because it is on a recovery path inconsistent with the database.'

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.
Post #1497292
Posted Monday, September 23, 2013 3:40 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 3:37 AM
Points: 466, Visits: 609
maintaining Log backup files is the most tricky part.

if u missed the chain then, everything becomes problematic.
Post #1497309
Posted Monday, September 23, 2013 6:36 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 39,910, Visits: 36,250
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 2008, MVP
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

Post #1497352
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse