Point-in-time recovery using Log file backups newer than full back

Hopefully I can ask this question w/o too much confusion. The example I am
about to give may not be practical, but the answer should help me understand
db and log backups a little better.
Scenario:
Full db backup performed 3 times in a day - morning, afternoon, and evening
(T1, T2, T3 respectively)
Log backup performed in morning (T1) and evening (T3) immediately after the
morning full db backups (no afternoon (T2) log backup)
Then server db drive fails after the evening db and log backups
The evening full backup is unrestorable (bad tape)
The evening log backup is good (stored on another device).
Could I restore the db to the point of failure using the afternoon full db
backup (T2) and the evening log backup (T3)?
db: T1--T2--T3
log: T1--T3
In other words, when restoring the evening log backup, against the afternoon
full db backup, would the restore process read through the evening log backup
to find the transactions begining at T2, or does the log being restored need
to be from a backup that occurs after the full backup was created?
My "guess" is that the restore would read through the T3 log and apply the
transactions that began after the T2 full db backup.If you ask whether you can "skip" a db backup, then the answer it yes. You cannot "skip" a log
backup, though. Perhaps easier with an examples. Say the time goes from top to bottom:
A Db
B Log
C Log
D Db
E Log
F Log
G Db
H Log
I Log
Here are a couple of examples of what you *can* do:
a, b, c, d, e, h, i
d, e, f, h, i
g, h, i
Here's a couple of examples of what you *cannot* do:
a, c, e, f, h, i
a, e, f, h, i
Short answer is that you need an unbroken chain of log backup. Db backups in between do not matter.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"GRakaska" <GRakaska@.discussions.microsoft.com> wrote in message
news:E20FF009-56C4-4B91-A31A-9720FB1404CD@.microsoft.com...
> Hopefully I can ask this question w/o too much confusion. The example I am
> about to give may not be practical, but the answer should help me understand
> db and log backups a little better.
> Scenario:
> Full db backup performed 3 times in a day - morning, afternoon, and evening
> (T1, T2, T3 respectively)
> Log backup performed in morning (T1) and evening (T3) immediately after the
> morning full db backups (no afternoon (T2) log backup)
> Then server db drive fails after the evening db and log backups
> The evening full backup is unrestorable (bad tape)
> The evening log backup is good (stored on another device).
> Could I restore the db to the point of failure using the afternoon full db
> backup (T2) and the evening log backup (T3)?
> db: T1--T2--T3
> log: T1--T3
> In other words, when restoring the evening log backup, against the afternoon
> full db backup, would the restore process read through the evening log backup
> to find the transactions begining at T2, or does the log being restored need
> to be from a backup that occurs after the full backup was created?
> My "guess" is that the restore would read through the T3 log and apply the
> transactions that began after the T2 full db backup.
>|||> A Db
> B Log
> C Log
> D Db
> E Log
> F Log
> G Db
> H Log
> I Log
> Here are a couple of examples of what you *can* do:
> a, b, c, d, e, h, i
> d, e, f, h, i
> g, h, i
I think an error has found it's way in the first row. You can't skip the F,
since that will break the log chain - and you also say that the chain can
not be broken :)
/Sjang|||> I think an error has found it's way in the first row.
Yes, thanks for catching that. It should have been:
a, b, c, e, f, h, i
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Henrik Davidsen" <none@.none.dk> wrote in message news:lBBDj.7440$9m.7234@.fe25.usenetserver.com...
>> A Db
>> B Log
>> C Log
>> D Db
>> E Log
>> F Log
>> G Db
>> H Log
>> I Log
>> Here are a couple of examples of what you *can* do:
>> a, b, c, d, e, h, i
>> d, e, f, h, i
>> g, h, i
>
> I think an error has found it's way in the first row. You can't skip the F,
> since that will break the log chain - and you also say that the chain can
> not be broken :)
> /Sjang
>
>