August 10, 2013 at 1:27 pm
Hi ,
let us suppose i take full backup f1 and now suppose i take 3 transaction log backup t1,t2,t3 .Now i can restore this transaction log backup provided i had restored full backup f1. But suppose before T3 i take full backup F2 then i cannot restore T3 untill F2 is restored.
But why this doesn`t happens in log shipping .I mean in log shipping even if we take full backups and if u have all transaction log backup`s provided there where no breakup`s in log backup`s chain, then to log shipping continues and it doesnt required to apply latest full backup on server(dr server)
August 10, 2013 at 3:16 pm
full backups do not truncate the log, therefore you can restore T3 with or without F2.
thats why log shipping is able to work, you can restore tranlog backups through full backups
---------------------------------------------------------------------
August 11, 2013 at 3:28 am
anoop.mig29 (8/10/2013)
But suppose before T3 i take full backup F2 then i cannot restore T3 untill F2 is restored.
Sure you can. Full backups do not in any way interfere in the log chain.
Did you try that scenario?
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
August 23, 2013 at 4:21 am
GilaMonster (8/11/2013)
anoop.mig29 (8/10/2013)
But suppose before T3 i take full backup F2 then i cannot restore T3 untill F2 is restored.Sure you can. Full backups do not in any way interfere in the log chain.
Did you try that scenario?
Yes i had tried it and what i found that i cant restore t3 if had restored f2 first,but this doesnt happens in logshiiping can u explain why
August 23, 2013 at 4:36 am
anoop.mig29 (8/23/2013)
GilaMonster (8/11/2013)
anoop.mig29 (8/10/2013)
But suppose before T3 i take full backup F2 then i cannot restore T3 untill F2 is restored.Sure you can. Full backups do not in any way interfere in the log chain.
Did you try that scenario?
Yes i had tried it and what i found that i cant restore t3 if had restored f2 first,but this doesnt happens in logshiiping can u explain why
Are you saying that you can't restore t3 without first restoring f2?
If your sequence of events is f1, t1, t2, f2, t3 then the following scenarios will get you the same results:
restore f1 (norecovery), t1 (norecovery), t2 (norecovery), t3 (recovery)
restore f2 (norecovery), t3(recovery)
August 23, 2013 at 4:40 am
anoop.mig29 (8/23/2013)
GilaMonster (8/11/2013)
anoop.mig29 (8/10/2013)
But suppose before T3 i take full backup F2 then i cannot restore T3 untill F2 is restored.Sure you can. Full backups do not in any way interfere in the log chain.
Did you try that scenario?
Yes i had tried it and what i found that i cant restore t3 if had restored f2 first,but this doesnt happens in logshiiping can u explain why
Then something explicitly broke your log chain, or you restored the full without NORECOVERY because full backups do not and never have broken the log chain.
You absolutely can take full backup b1, full backup b2, differential backup d1 then log backup L1 and then restore b1 and L1 and it will work.
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
August 23, 2013 at 4:55 am
Can you tell the error message you got when you tried to restore the third LOG backup? This could give some information about why the chain was broken. It could be someone else made a log backup inbetween?
August 23, 2013 at 5:55 am
Lynn Pettis (8/23/2013)
anoop.mig29 (8/23/2013)
GilaMonster (8/11/2013)
anoop.mig29 (8/10/2013)
But suppose before T3 i take full backup F2 then i cannot restore T3 untill F2 is restored.Sure you can. Full backups do not in any way interfere in the log chain.
Did you try that scenario?
Yes i had tried it and what i found that i cant restore t3 if had restored f2 first,but this doesnt happens in logshiiping can u explain why
Are you saying that you can't restore t3 without first restoring f2?
If your sequence of events is f1, t1, t2, f2, t3 then the following scenarios will get you the same results:
restore f1 (norecovery), t1 (norecovery), t2 (norecovery), t3 (recovery)
restore f2 (norecovery), t3(recovery)
GilaMonster (8/23/2013)
anoop.mig29 (8/23/2013)
GilaMonster (8/11/2013)
anoop.mig29 (8/10/2013)
But suppose before T3 i take full backup F2 then i cannot restore T3 untill F2 is restored.Sure you can. Full backups do not in any way interfere in the log chain.
Did you try that scenario?
Yes i had tried it and what i found that i cant restore t3 if had restored f2 first,but this doesnt happens in logshiiping can u explain why
Then something explicitly broke your log chain, or you restored the full without NORECOVERY because full backups do not and never have broken the log chain.
You absolutely can take full backup b1, full backup b2, differential backup d1 then log backup L1 and then restore b1 and L1 and it will work.
let us say my backup sequence is f1,t1,t2,t3,f2,t4
Now suppose my restore sequence with standby option is is f1 t1,t2,f2
now if i try to restore t3 it gives error so i apply t4 which allows to do so (all with standby option)
Now in loshiping it allows to restore t3 even if i had restored f2 first(with standby,replace)
why the difference
August 23, 2013 at 6:02 am
You wouldn't restore f3 in that scenario, there's no point it will just overwrite what's already there. Log shipping does not ship and restore full backups, that would be an appalling waste of network bandwidth and time
What you would do (and what log shipping does) is restore f1, t1, t2, t3.
That will work as full backups do not ever break the log chain.
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
August 23, 2013 at 6:14 am
anoop.mig29 (8/23/2013)
Now in loshiping it allows to restore t3 even if i had restored f2 first(with standby,replace)
why the difference
I don't know about the built-in logshipping feature because we use a custom made solution. But most probably the logshipping will try to restore the t3. It will fail with a "database too recent to apply" message and therefor the logshipping will continue with restoring the next t-log backup. So the t3 backup is not restored, but it won't fail the logshipping process either.
August 23, 2013 at 6:26 am
GilaMonster (8/23/2013)
You wouldn't restore f3 in that scenario, there's no point it will just overwrite what's already there. Log shipping does not ship and restore full backups, that would be an appalling waste of network bandwidth and timeWhat you would do (and what log shipping does) is restore f1, t1, t2, t3.
That will work as full backups do not ever break the log chain.
I think u are not getting what i am saying
Basically in normal situation
Suppose my backup sequence is f1,t1,t2,t3,f2,t4
Now this sql allows f1,t1,t2,t3,f2(replace),t4 (all with standby options).
but sql doent allows this f1,t1,t2,f2(replace) then t3 fails and need to apply t4 or i should n`t be using replace (all with standby options)
But with logshiping allows/does f1(manual restore,with standby option),t1,t2(logship restore job),f2(replace,restore manually full backup) then t3(logship restore job) (all with standby options).
tell me why such difference
or i am missing something
August 23, 2013 at 6:35 am
There is no difference!
But with logshiping allows/does f1(manual restore,with standby option),t1,t2(logship restore full backup job),f2(replace,restore manually full backup) then t3(logship restore job) (all with standby options).
No, log shipping does NOT do that. Log shipping ships the logs. Log shipping does f1 (restored manually to initialise the secondary), t1, t2, t3, t4, etc. It will never copy and restore another full backup.
Manually you would do exactly the same, f1, t1, t2, t3, t4 and so on and it does work.
t3 fails in your example because it was taken before f2 and when you restored f2 that t3 log was now too early to apply (and the error states that very clearly). But then, you wouldn't ever do a restore sequence of f1, t1, t2, t3, f2, t4 as that would be a complete and total waste of time. You'd just restore f2 and t4 (all log backups taken after the full backup, not ones from before) or if you didn't have f2 you'd restore f1, t1, t2, t3, t4
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
August 23, 2013 at 6:52 am
Hi Gail,
I think "anoop.mig29" means (correct me if I'm wrong):
Restore f1 as a logshipping target and then restore t1 and t2 with the logshipping process. Then manually restore f2 on the target with a "with replace, standby". When he continues manually restoring t3 it fails and raises an error.
The automated logshipping proces will also get this same error, but will continue with restoring the next file. The poster thinks t3 is restored by the logshipping process.
@anoop.mig29: the t3 is not restored in the logshipping process. But this error won't fail the logshipping process.
August 23, 2013 at 10:33 am
HanShi (8/23/2013)
Hi Gail,I think "anoop.mig29" means (correct me if I'm wrong):
Restore f1 as a logshipping target and then restore t1 and t2 with the logshipping process. Then manually restore f2 on the target with a "with replace, standby". When he continues manually restoring t3 it fails and raises an error.
The automated logshipping proces will also get this same error, but will continue with restoring the next file. The poster thinks t3 is restored by the logshipping process.
@anoop.mig29: the t3 is not restored in the logshipping process. But this error won't fail the logshipping process.
Hi HanShi thanks u got it right
Actually situation is something like this there is one primary and 2 secondary . i have to bring one secondary up but cannot brak logshipping between primary and other secondary plus primary has to be avaliable , for a dr drill .this is giving toubles because i have know idea how would i bring primary and other dr in synch afetr dr drill
August 23, 2013 at 10:47 am
Once more with feeling...
Full backups DO NOT break the log chain.
Therefore, to re-initialise the secondary after the drill, take a full backup of the primary, copy it, restore it, restart the log copying jobs. Done.
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 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply