April 30, 2010 at 7:39 am
anilkullam (4/30/2010)
As for as my knowledge is concerned, a differential backup shud have a base to start with, which is necessarily the recent full backup available (here for wednesdays diff backups, the base is the tuesdays full backup taken at 6pm. If this file is corrupted how are we gonna restore to point in time recovery even though we were able to take tail-log and subsequent differ backups.
Christian Büttner posted a great demonstration script just a few posts earlier. Try it out, you'll see it works just fine.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 30, 2010 at 8:17 am
What is a tail log?
April 30, 2010 at 8:34 am
I think this question really makes me think. I thought the log backup chain was broken after a full backup based on this I read on MSDN
"If a log backup becomes missing or damaged, start a new log chain by creating a full or differential database backup and then backing up the transaction log to start a new log chain. We recommend that you retain transaction logs backups that come before a missing log backup, in case you ever want to restore the database to a point in time within those backups. For information about how to help protect your backups, see Security Considerations for Backup and Restore (SQL Server)."
April 30, 2010 at 8:39 am
jswedlund (4/30/2010)
What is a tail log?
A tail-log backup is a backup you make of the active transaction log before starting an emergency repair. Withouut it, you will lose all the data up to the last log backup, with a tail-log backup, you can restore up to the moment of failure.
April 30, 2010 at 8:41 am
Lynn Pettis (4/30/2010)
..
.
Full and differential backups do not break the log chain of transaction log backups. In this scenerio, the Tuesday night backup is corrupt, and you need to recover to a point in time at 9:00 AM on Wednesday. This is possible by using the full backup from Monday night, the most recent differential taken after the Monday night full backup and Tuesday nights full backup, ie the one taken at 4:00 PM on Tuesday, then restoring all the transaction log backups taken since 4:00 PM Tuesday.
Should that be "...the most recent differential taken after the Monday night full backup and <BEFORE> Tuesday night's full backup, ..."
My understanding is that differential backups just backup the differences since the last full backup. Since Tuesday's full backup was corrupted, then all the differential backups based on that corrupt backup are useless. Is that correct?
April 30, 2010 at 8:54 am
Great question.
I did not find it ambiguous, in fact this line removed the ambiguity.
Restore all transaction log backups taken since the latest differential backup restored with norecovery.
April 30, 2010 at 8:58 am
jswedlund (4/30/2010)
What is a tail log?
See Tail-Log Backups for all the details.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 30, 2010 at 9:04 am
My problem is that I overthink the answer.
To me the answer was yes, the suggested scenario would work. It seemed obvious. It seemed it couldn't be that simple. There is a reason why it wouldn't work and I wasn't seeing it.
Regardless, it is a very good question and a basic one for DBAs.
April 30, 2010 at 9:07 am
Bradley Deem (4/30/2010)
Great question. I did not find it ambiguous, in fact this line removed the ambiguity.Restore all transaction log backups taken since the latest differential backup restored with norecovery.
Hmm. Does "restored with recovery" refer to the logs or the differential, I wonder? 😛
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 30, 2010 at 9:18 am
hanziou (4/30/2010)
Lynn Pettis (4/30/2010)
..
.
Full and differential backups do not break the log chain of transaction log backups. In this scenerio, the Tuesday night backup is corrupt, and you need to recover to a point in time at 9:00 AM on Wednesday. This is possible by using the full backup from Monday night, the most recent differential taken after the Monday night full backup and Tuesday nights full backup, ie the one taken at 4:00 PM on Tuesday, then restoring all the transaction log backups taken since 4:00 PM Tuesday.
Should that be "...the most recent differential taken after the Monday night full backup and <BEFORE> Tuesday night's full backup, ..."
My understanding is that differential backups just backup the differences since the last full backup. Since Tuesday's full backup was corrupted, then all the differential backups based on that corrupt backup are useless. Is that correct?
Yes it should. I thought I had actually typed that word, I know I thought it. Good Catch!
April 30, 2010 at 9:30 am
SQL Dude-467553 (4/30/2010)
I think this question really makes me think. I thought the log backup chain was broken after a full backup based on this I read on MSDN"If a log backup becomes missing or damaged, start a new log chain by creating a full or differential database backup and then backing up the transaction log to start a new log chain. We recommend that you retain transaction logs backups that come before a missing log backup, in case you ever want to restore the database to a point in time within those backups. For information about how to help protect your backups, see Security Considerations for Backup and Restore (SQL Server)."
If a t-log backup becomes damaged or missing, yes, you need to start a new log chain by performing a full or differential backup. The key here is that the full and differential backups themselves don't BREAK a log chain.
April 30, 2010 at 9:31 am
LOL This is just like one of the certification questions. Nice one, Lynn.
April 30, 2010 at 9:55 am
Nice Marmot (4/30/2010)
LOL This is just like one of the certification questions. Nice one, Lynn.
Thanks. I guess I'm glad I haven't taken any of the certification tests.
April 30, 2010 at 10:13 am
Steve Jones - Editor (4/30/2010)
CirquedeSQLeil (4/29/2010)
Thanks Lynn.The part that gets me with these questions is if the hardware failure was enough to corrupt the database and the full backup - it would seem to me that you would be unable to perform a tail log backup. After all the failure had to be severe enough that it was able to corrupt files on different drives / luns. Thus I always have to step back and think a little more about it. It must not have been too severe of a hardware failure because the database was able to be brought back online.
Not necessarily, I think you're caught up in the high end systems. Lots of people have logs/data/bus on one set of disks, often R5. Possible that someone could scribble on a backup and data file and not necessarily the log file. Or enough of the data file to make it necessary to restore.
Good point.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 30, 2010 at 10:20 am
Nice Marmot (4/30/2010)
LOL This is just like one of the certification questions. Nice one, Lynn.
I imagine there are plenty of these types of questions on the current exams. There were plenty for the older versions.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 31 through 45 (of 60 total)
You must be logged in to reply to this topic. Login to reply