March 16, 2009 at 11:26 am
Hi,
A Database has two full back ups run the same night, under different routines. Say Back Up A and Back Up B.
The following day the Maintenance Plan for Back Up A backs up the Transaction Logs. System B does not.
If I restore to a point in time, using the Transaction logs can I use either Back Up A or Back Up B, with the logs in sequence according to LSNs? Or can I only use Back Up A - whose Mainenance Plan went on to back up the transaction log?
How do the LSNs for a full back up relate to the Transaction Log Back Up LSNs?
Interesting.
Thanks Colin
March 16, 2009 at 1:41 pm
You can restore either full backup followed by all the logs, in sequence, since that backup. Full backups do not truncate the log.
Backup A
Log backup 1
Log backup 2
Log backup 3
Backup B
Log backup 4
Log backup 5
Log backup 6
So in this case, you have two options to restore
Backup A, Log backup 1, Log backup 2, Log backup 3, Log backup 4, Log backup 5, Log backup 6
Or
Backup B, Log backup 4, Log backup 5, Log backup 6
The only time that you cannot use the earlier backup is if you have differential 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
March 17, 2009 at 4:08 am
Gail,
Thank you for your response,very helpful.
Do you know the relationship between the LSN for back Up and Transaction log files? For Example
Type First LSN Last LSN
Transaction 83866000 48992000
Full Back Up 84604000 84604000
Full Back Up 84404000 84405000
Transaction 83834000 83866000
The Transaction numbers make sense, but the full Back Up numbers do not seem to be linked.
Colin
March 17, 2009 at 4:33 am
I use this script to generate the restore log sequence(s).
(and the header time indication may help to determine which full backup to start from )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 17, 2009 at 7:19 am
Colin Betteley (3/17/2009)
The Transaction numbers make sense, but the full Back Up numbers do not seem to be linked.
Because they aren't. Log backups form a chain, the LSN that one ends on is the LSN that the next starts on. Full backups, as I indicated, do not truncate the log and hence are not part of the log chain (with the exception of the one that starts the log chain). All the LSNs of the full backups indicate is the piece of the log that they backed up to ensure consistency.
To restore you need a full backup and then all the log backups that ran after it.
Transaction 83866000 48992000
That doesn't make any sense. The ending LSN is way lower than the starting.
Full Back Up 84604000 84604000
Full Back Up 84404000 84405000
Transaction 83834000 83866000
Those don't look chronological. It looks like the transaction log backup there has a lower LSN (starting and ending) than the two full 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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply