Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / DB Engine / SQL Server / March 2008

Tip: Looking for answers? Try searching our database.

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

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
GRakaska - 17 Mar 2008 15:23 GMT
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.
Tibor Karaszi - 17 Mar 2008 15:49 GMT
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.
Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> 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
[quoted text clipped - 25 lines]
> My "guess" is that the restore would read through the T3 log and apply the
> transactions that began after the T2 full db backup.
Henrik Davidsen - 17 Mar 2008 22:43 GMT
> A Db
> B Log
[quoted text clipped - 10 lines]
> 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
Tibor Karaszi - 17 Mar 2008 23:01 GMT
> 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

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

>> A Db
>> B Log
[quoted text clipped - 16 lines]
>
> /Sjang
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.