Backups

Question

Backups

Viewing 15 posts - 46 through 60 (of 60 total)

You must be logged in to reply to this topic. Login to reply

Richard M. SSCertifiable Points: 7401 More actions · Answer 1

Nice one Lynn. Very good question... and something learned 🙂

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

Tom Garth SSCertifiable Points: 6193 More actions · Answer 2

I did just take the cert exam, and I'm glad this one wasn't on it. I too thought that a full backup broke the log chain.

Thanks for a good and educational question.

Tom Garth
Vertical Solutions[/url]

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Shriniket Ten Centuries Points: 1356 More actions · Answer 3

In the recovery steps use of "latest differential backup" is misleading; I know it does say Tuesday @4pm but shouldn't the "latest differential" be Wednesday @8am? What would be accurate is if it said "latest differential based on Mondays Full Backup"

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Shriniket (4/30/2010)
In the recovery steps use of "latest differential backup" is misleading; I know it does say Tuesday @4pm but shouldn't the "latest differential" be Wednesday @8am? What would be accurate is if it said "latest differential based on Mondays Full Backup"

This one has come up a couple of times, so I'll address it. First, sorry if I didn't spell everything out in explicit detail. With that said, it is a matter of interpration based on context. In the context of the scenario, the restore is starting with the Monday night full backup, what is the latest differential backup? Answer, not the one taken at 8:00 AM Wednesday.

If I tell my co-worker I am restoring the Monday night full backup, tell me what the lastest differential backup is while I get this started. I would expect him (or her) to look up the correct file with my having to specify "based on the Mondays Full Backup."

Now, if this individual is a new junior DBA, and wasn't sure, I'd expect them to ask. I'd also be making sure that they learn about backup/restores as they may find themselves having to do it when I'm not available to assist.

Shriniket Ten Centuries Points: 1356 More actions · Answer 5

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 🙂

magasvs SSCertifiable Points: 7660 More actions · Answer 6

I replied "No" because I would restore to the point of time when restored the last log backup.

jlennartz SSCommitted Points: 1574 More actions · Answer 7

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 SSC Guru Points: 442467 More actions · Answer 8

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.

Jeffrey Williams SSC Guru Points: 90305 More actions · Answer 9

Lynn, very good question - I got it right mostly because I have been harping on the same thing that you have 🙂

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 10

I misread the question. Got the point only after looking at some posts :ermm:

"Keep Trying"

Minnesota - Viking Hall of Fame Points: 3469 More actions · Answer 11

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..

[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]

B's-Data SSChasing Mays Points: 607 More actions · Answer 12

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 SSC Veteran Points: 202 More actions · Answer 13

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 SSC Eights! Points: 936 More actions · Answer 14

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 SSC Enthusiast Points: 180 More actions · Answer 15

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