June 8, 2009 at 2:48 pm
Hi,
I have a sql server 2005 database, with full backup (every night), differential backup (every 15 minutes), TLog backup (Every minute).
I am restoring these backup sets for a point in time recovery in following sequence.
1-Restore Full backup with NORECOVERY
2-Restore most recent Differential backup with NORECOVERY
3-Restore the TLogs backups created after the last differential backups with NORECOVERY
4-Restore the database with RECOVERY
but i am getting an error in the second step, when i tried to restore the most recent differential backup i got following error.
"
Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 149014000003456800006, which is too recent to apply to the database. An earlier log backup that includes LSN 148750000003326500001 can be restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
"
fyi..I am not shrinking or truncating any backup or TLog.
June 8, 2009 at 4:58 pm
Are you sure you got the error when restoring the differential backup? I've only ever seen that when restoring log backups and I've gotten them out of order. Log restores have to be chronological, but differential restores don't.
Could you post the actual restore commands you're running?
Greg
June 8, 2009 at 10:56 pm
Hi,
U need to take all t-log backup after recent differencial backup. I think u r taking onle t-log for restore.
June 8, 2009 at 11:12 pm
You don't need to take log backups. That advice is misplaced.
I don't think this will occur with a diff, but it's possible if the diff is not related to the full backup.
The most recent diff only corresponds to the most recent full backup BEFORE it. Be sure you are restoring those files in that order.
June 9, 2009 at 12:12 am
Agreed with Steve ...please ensure that you are restoring the right Differential backup ....
One more point came to my mind but i am confident that i am wrong 🙂
I was thinking that you are taking log backup every 1 min and diff every 15 mins ...there might be a situation where your log and differential backups both will take place at the same time or fraction of seconds away ...in that can there might be a possiblility that some LSN might be written to log and not to Diff backup ...
As i already said "looks like i am wrong".....it should copy the LSN sequence to both the t-log and Diff log .thats how diff works ...
I just shared as somehow " LSN leak " sort of concept came to my mind ..
HTH
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 9, 2009 at 2:56 am
usman.tanveer (6/8/2009)
but i am getting an error in the second step, when i tried to restore the most recent differential backup i got following error.
That error comes from restoring a log, not a differential, and it's because you've missed a log while restoring, or maybe because the diff didn't restore with a different error.
Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 149014000003456800006, which is too recent to apply to the database. An earlier log backup that includes LSN 148750000003326500001 can be restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
If it was the restore of the diff that was failing, the last line would read "RESTORE DATABASE is terminating abnormally"
p.s. diffs every 15 min and log backups every minute? That's a lot of backups.
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
June 9, 2009 at 11:07 am
GilaMonster (6/9/2009)p.s. diffs every 15 min and log backups every minute? That's a lot of backups.
This is becuase we have log shipping implemented for high availability, do you still think it's alot?
if yes, what is the best practice?or how should we decide the intervals?
June 9, 2009 at 11:12 am
How much data can you afford to lose in the case of a disaster? That's the main question that identifies the frequency for log backups.
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
June 9, 2009 at 11:18 am
This is a critical product licensing system so cannot loose more than one minute of data that's why we selected these intervals. And we didn't see any performance hit due to this interval (or this many backups) so we didn't modify it.
We can certainly reduce the interval for differential, but we selected 15 minutes to minimize the number of TLog backups that we will need to restore for manually restoring the database.
June 9, 2009 at 11:54 am
Diffs don't impact log backups. At least I didn't think they did. They only reduce the number of log restores needed, but AFAIK (correct me Gail), you could take the full and all t-logs since the full, regardless of having the diff's to get to a point in time.
Diffs every 15 minutes is a lot, especially as they keep growing to get you the changes since the last full. What's the full schedule?
I'd also look at mirroring if this is critical. You can have log shipping to multiple backups if needed, as well as mirroring set up to ensure no data loss.
June 9, 2009 at 12:18 pm
Steve Jones - Editor (6/9/2009)
Diffs don't impact log backups. At least I didn't think they did. They only reduce the number of log restores needed, but AFAIK (correct me Gail), you could take the full and all t-logs since the full, regardless of having the diff's to get to a point in time.
100% correct. Neither fulls nor diffs break the log chain so you can restore from any full or diff, provided you have all the log backups from that full/diff backup up to the point you're restoring to.
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
June 9, 2009 at 12:57 pm
Steve, you are right. If you have full backup and all tlog backups in sequence afterwards, no matter whether there is any diff backup in between or not, you can still get point-in-time recovery.
I personally think 1m tlog backup is too much. 🙂
June 9, 2009 at 1:01 pm
djjwu (6/9/2009)
I personally think 1m tlog backup is too much. 🙂
For any particular reason (performance, IO, space etc) or just the best practice?
Agreed that it's too much but then what is the right interval considering the fact that every transaction is important?
June 9, 2009 at 1:07 pm
usman.tanveer (6/9/2009)
djjwu (6/9/2009)
I personally think 1m tlog backup is too much. 🙂For any particular reason (performance, IO, space etc) or just the best practice?
Agreed that it's too much but then what is the right interval considering the fact that every transaction is important?
This is where I would say a mirrored db with a secondary (1 or more) log shipped db with an interval of 5 to 15 minutes. Not sure how this would be handled in a fai-over situation, but worth investigating at least.
June 9, 2009 at 1:25 pm
Lynn Pettis (6/9/2009)[hrThis is where I would say a mirrored db with a secondary (1 or more) log shipped db with an interval of 5 to 15 minutes. Not sure how this would be handled in a fai-over situation, but worth investigating at least.
The problem with mirroring would be, in HA mode it will not commit the transaction on primary untill commited on secondary, which could be a performance hit as the secondary server is at a remote location. Also if it failed to commit on secondary it will roll back the transaction on primary BUT we cannot aford transaction failure AND transaction delay on primary due to any HA architecture.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply