SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backups


Backups

Author
Message
magasvs
magasvs
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1501 Visits: 753
I replied "No" because I would restore to the point of time when restored the last log backup.
jlennartz
jlennartz
SSC Eights!
SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)

Group: General Forum Members
Points: 830 Visits: 1197
Good learn question for me Lynn.

I got it wrong because I analysted it to much and now hind sight is always better than foresight.

I figured the Wednesday 8AM Differential should be the one to use as it was the last differntial but if you used it you would miss all transactions from the 4PM Tuesday difference until the 6PM Tuesday Full becasue the Wednesday differentials use the Tuesday 6PM Full as a starting point.

Thanks for the excellent QotD Lynn.
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39305 Visits: 38529
Shriniket (4/30/2010)
Lynn Pettis (4/30/2010)... With that said, it is a matter of interpration based on context. ...


I think that says it, maybe this could have been a trick question where latest differential did mean Wed 8am which would question one's knowledge of how back ups work ... any how ... please don't apologize, very good question and makes for interesting conversation :-)


Actually, I was trying to be a bit sarcastic with the apology, guess that didn't translate very well in the written word. The way I said it as I typed it, that is another story.

Thank you for the feedback and I am glad you thought the question was a good one.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7691 Visits: 9966
Lynn, very good question - I got it right mostly because I have been harping on the same thing that you have Smile

For those interested, the other option for restoring to a point in time would be:

1) Restore full backup from Monday with norecovery
2) Restore all transaction log backups from Monday to 9am Wednesday with norecovery
3) Restore tail log backup with recovery

Or, restore any differential taken before the Tuesday night full and after the Monday night full, and then restore all transaction log backups to the point in time you want.

The important thing here is that you have an unbroken log chain (transaction log backups). As long as you have that, you can restore from any full backup.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

ChiragNS
ChiragNS
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3387 Visits: 1865
I misread the question. Got the point only after looking at some posts Ermm

"Keep Trying"
Minnesota - Viking
Minnesota - Viking
Old Hand
Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)

Group: General Forum Members
Points: 307 Visits: 564
I got it WRONG

But i love this part of your reply to "awoodhouse"


Wrong, you have to use the most recent differential backup based on the full backup file you are restoring, not just the most recent differential taken. Remember the full backup starts the base for subsequent differential backups.
--Lynn Pettis


Coz i was thinking in this lines....
I was scratching my head thinking that Latest Diff Back up is on Wed Morning @ 8 AM

Awesome..


Do you have a blog or site of your own..
I am looking for such tricky questions in the DBA Track..


Today is the tomorrow you worried about yesterday:-)

B's-Data
B's-Data
SSC Veteran
SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)

Group: General Forum Members
Points: 298 Visits: 532
Hi all, I don't typically comment on these questions but I must chime in on this one. I agree with those who think this answer is incorrect. Based on Microsoft's own definition of a Differential backup: A backup of all files in the database. This backup contains only the data extents that were modified since the most recent database backup of each file.", there is no way that restoring Tuesday's differential would allow you to have the ability to successfully complete the point in time restore. I have done a LOT of restores and have even had to in an emergency situation but I have never considered or performed this type of restore; but I will.
prakskarry
prakskarry
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 172
The answer would be 'YES' here as FULL BACKUP Of Tuesday is curropt and following differential backups will capture the extents changed since last full backup.I got traped in the question.
sbamaty
sbamaty
SSChasing Mays
SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)

Group: General Forum Members
Points: 652 Visits: 198
I misread the question too...when I got my answer incorrect, I went back and re-read and I was like "Stupid me". Anyway, after all, good question.
navneet.agarwal19
navneet.agarwal19
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 68
A Big confusion is there.

F1 full backup
T1 Log Backup
D1 Differential Backup
T2 Log Backup
D2 Differential Backup
T3 Log Backup
F2 full backup
T4 Log Backup
D3 Differential Backup
T5 Log Backup
D4 Differential Backup
T6 Log Backup

Now If F2 is corrupt. To restore database, the sequence :
F1, D4, T6
or F1, D2, T3, T4, T5, T6

I don't think F1, D3, T5, T6 works
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search