InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 06:01:53 GMT20http://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspxI 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.Fri, 15 Oct 2010 09:53:05 GMTsbamatyhttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspxThe 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.Fri, 23 Jul 2010 01:03:09 GMTprakskarryhttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspxHi 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.Fri, 07 May 2010 08:02:42 GMTbgdjwoodshttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspxI got it WRONGBut i love this part of your reply to "awoodhouse"[b][i]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[/i][/b]Coz i was thinking in this lines....I was scratching my head thinking that Latest Diff Back up is on Wed Morning @ 8 AMAwesome.. Do you have a blog or site of your own.. I am looking for such tricky questions in the DBA Track..Wed, 05 May 2010 19:06:51 GMTMinnesota - Viking http://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspxI misread the question. Got the point only after looking at some posts :ermm:Mon, 03 May 2010 04:24:19 GMTChiragNShttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspxLynn, 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 norecovery2) Restore all transaction log backups from Monday to 9am Wednesday with norecovery3) Restore tail log backup with recoveryOr, 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.Fri, 30 Apr 2010 16:35:25 GMTJeffrey Williams 3188http://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspx[quote][b]Shriniket (4/30/2010)[/b][hr][quote][b]Lynn Pettis (4/30/2010)[/b]... With that said, it is a matter of interpration based on context. ...[/quote]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 :-)[/quote]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.Fri, 30 Apr 2010 15:00:29 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspxGood 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.Fri, 30 Apr 2010 14:31:55 GMTjlennartzhttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspxI replied "No" because I would restore to the point of time when restored the last log backup.Fri, 30 Apr 2010 12:57:24 GMTmagasvshttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspx[quote][b]Lynn Pettis (4/30/2010)[/b]... With that said, it is a matter of interpration based on context. ...[/quote]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 :-)Fri, 30 Apr 2010 12:52:35 GMTShrinikethttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspx[quote][b]Shriniket (4/30/2010)[/b][hr]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"[/quote]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.Fri, 30 Apr 2010 12:39:25 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspxIn 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"Fri, 30 Apr 2010 11:39:31 GMTShrinikethttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspxI 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.Fri, 30 Apr 2010 10:54:49 GMTTom Garthhttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspxNice one Lynn. Very good question... and something learned :)Fri, 30 Apr 2010 10:42:04 GMTRichard M.http://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspx[quote][b]Nice Marmot (4/30/2010)[/b][hr]LOL This is just like one of the certification questions. Nice one, Lynn.[/quote]I imagine there are plenty of these types of questions on the current exams. There were plenty for the older versions.Fri, 30 Apr 2010 10:20:01 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspx[quote][b]Steve Jones - Editor (4/30/2010)[/b][hr][quote][b]CirquedeSQLeil (4/29/2010)[/b][hr]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.[/quote]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.[/quote]Good point.Fri, 30 Apr 2010 10:13:40 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspx[quote][b]Nice Marmot (4/30/2010)[/b][hr]LOL This is just like one of the certification questions. Nice one, Lynn.[/quote]Thanks. I guess I'm glad I haven't taken any of the certification tests.Fri, 30 Apr 2010 09:55:37 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspxLOL This is just like one of the certification questions. Nice one, Lynn.Fri, 30 Apr 2010 09:31:49 GMTNice Marmothttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspx[quote][b]SQL Dude-467553 (4/30/2010)[/b][hr]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)."[/quote]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.Fri, 30 Apr 2010 09:30:45 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspx[quote][b]hanziou (4/30/2010)[/b][hr][quote][b]Lynn Pettis (4/30/2010)[/b][hr]...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.[/quote]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?[/quote]Yes it should. I thought I had actually typed that word, I know I thought it. Good Catch!Fri, 30 Apr 2010 09:18:35 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspx[quote][b]Bradley Deem (4/30/2010)[/b][hr]Great question. I did not find it ambiguous, in fact this line removed the ambiguity.[quote]Restore all transaction log backups taken since the latest differential backup [b]restored[/b] with norecovery. [/quote][/quote]Hmm. Does "restored with recovery" refer to the logs or the differential, I wonder? :-PFri, 30 Apr 2010 09:07:53 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspxMy 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.Fri, 30 Apr 2010 09:04:43 GMTSteve Cullenhttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspx[quote][b]jswedlund (4/30/2010)[/b][hr]What is a tail log?[/quote]See [url=http://msdn.microsoft.com/en-us/library/ms179314.aspx]Tail-Log Backups[/url] for all the details.Fri, 30 Apr 2010 08:58:18 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspxGreat question.I did not find it ambiguous, in fact this line removed the ambiguity.[quote]Restore all transaction log backups taken since the latest differential backup [b]restored[/b] with norecovery. [/quote]Fri, 30 Apr 2010 08:54:04 GMTBradley Deemhttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspx[quote][b]Lynn Pettis (4/30/2010)[/b][hr]...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.[/quote]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?Fri, 30 Apr 2010 08:41:05 GMThanziouhttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspx[quote][b]jswedlund (4/30/2010)[/b][hr]What is a tail log?[/quote]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.Fri, 30 Apr 2010 08:39:17 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspxI 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)."Fri, 30 Apr 2010 08:34:02 GMTSQL Dude-467553http://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspxWhat is a tail log?Fri, 30 Apr 2010 08:17:51 GMTjswedlundhttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspx[quote][b]anilkullam (4/30/2010)[/b][hr]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.[/quote]Christian Büttner posted a great demonstration script just a few posts earlier. Try it out, you'll see it works just fine.Fri, 30 Apr 2010 07:39:38 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspx[quote][b]anilkullam (4/30/2010)[/b][hr][font="Comic Sans MS"]Hi Lynn & everybody else..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.My point is: If we make use of mondays full backup which is not the base for latest differ backup, how is consistency maintained as we will be missing differ backups from monday full backup time to tuesday full backup time.Pls get to me, if im not clear...also leave a msg to anilkullam@gmail.comwaiting for potential explanation..thank you,[/font][/quote]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 before 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.Edit: Added a missing word (before).Fri, 30 Apr 2010 07:30:01 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspx[quote][b]CirquedeSQLeil (4/29/2010)[/b][hr]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.[/quote]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.Fri, 30 Apr 2010 07:13:08 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspx[font="Comic Sans MS"]Hi Lynn & everybody else..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.My point is: If we make use of mondays full backup which is not the base for latest differ backup, how is consistency maintained as we will be missing differ backups from monday full backup time to tuesday full backup time.Pls get to me, if im not clear...also leave a msg to anilkullam@gmail.comwaiting for potential explanation..thank you,[/font]Fri, 30 Apr 2010 06:40:43 GMTanilkullamhttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspx[quote][b]Mike McIver (4/30/2010)[/b][hr]Wouldn't the log chain be broken with the Tues 6p full?[/quote]No. A full backup does not break the log chain.Fri, 30 Apr 2010 05:28:37 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspxWouldn't the log chain be broken with the Tues 6p full? Thereafter diff's or trans backups would be inapplicable to prior full backups making the recovery point-in-time the last log backup prior to the Tues 6p full. ((?))Fri, 30 Apr 2010 05:07:46 GMTMike McIverhttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspx[quote][b]Hugo Kornelis (4/30/2010)[/b][hr]Great question, reminding people of an often-misunderstood and very important fact.One minor letdown (hey! Other people moan about my questions, I get to moan back;-)) is the ambiguity in the described steps:"Restore the latest differential backup, Tuesday @ 4:00 PM, with norecovery."As other already indicated, that diff backup was not the lastest. The latest diff was Wednesday 8:00 AM. I lost (once more) a point by being too hasty - I saw the "latest differential" and without bothering to actually read the rest, I immediately concluded "heck no, that won't work; the latest diff is based on the tuesday full - the poor chap will have to revert to monday and restore a lot more log backups".So I answered "no", then found I missed a ppoint because I understood the concept but failed to read that the latest diff backup was not actually the latest diff backup, but rather the latest diff backup that was based on monday's full.Ah well. Still a great question. But if the description had read "Restore the differential backup from Tuesday @ 4:00 PM, with norecovery" (i.e., without the mislaeading word "latest"), it would have been a superb question.[/quote]This hit me also... I had to carefully reread [strike]this[/strike] the question to realize why I got it wrong. :(Fri, 30 Apr 2010 04:32:13 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspx[quote][b]Christian Buettner-167247 (4/30/2010)[/b][hr]Hi Paul, thanks for your reply. I managed to get it done with your second option, but the first option does not work for me. The screen looks exactly like your first screencap, except for the row highlighted in blue being absent in my SSMS. Refreshing did not help.[/quote]:cool:[quote]Maybe this is related to my version of SQL Server (9.00.4053.00)?[/quote]Possibly. My versions:Microsoft SQL Server 2008 (SP1) - [b]10.0.2766.0[/b] (Intel X86) Feb 25 2010 13:18:40 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)Microsoft SQL Server 2005 - [b]9.00.4285.00[/b] (Intel X86) Feb 8 2010 23:45:10 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)More than likely this is an SSMS 2008 improvement - although I have both 2005 and 2008 servers above, I do not have a 2005 SSMS to hand :(Fri, 30 Apr 2010 04:20:46 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspxHi Paul,thanks for your reply. I managed to get it done with your second option, but the first option does not work for me. The screen looks exactly like your first screencap, except for the row highlighted in blue being absent in my SSMS. Refreshing did not help.Maybe this is related to my version of SQL Server?[quote]Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)[/quote]Thanks!Fri, 30 Apr 2010 04:07:03 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspx[quote][b]Christian Buettner-167247 (4/30/2010)[/b][hr]Not sure what you mean. Just to clarify my question a bit: I have restored everything, except the tail log backup (and I am in no-recovery). If I then start up SSMS and try to restore just the log, I cannot restore, because I cannot "Select the Backup Sets to restore". To me this is one step at a time, but maybe I have still missed something? Thanks![/quote]Yes, that's the same steps I used (I ran it fully through the UI once; then from T-SQL to the restore-tail stage, then UI):BACK : Tasks -> Restore -> Database -> From Device (select 'C:\temp\BACK5Lognorecovery.bak')[img]http://www.sqlservercentral.com/Forums/Attachment5833.aspx[/img]ORBACK : Tasks -> Restore -> Transaction Log[img]http://www.sqlservercentral.com/Forums/Attachment5834.aspx[/img]You might have to refresh the status of the database in Object Explorer to get the Transaction Log restore option to be available...Pauledit: to fix imagesFri, 30 Apr 2010 03:55:31 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspx[quote][b]Paul White NZ (4/30/2010)[/b][hr][quote][b]Christian Buettner-167247 (4/30/2010)[/b][hr]Tested it with SSMS and failed ("Select the Backup Sets to restore" shows no entries after selecting the tail backup in "From device:")...If anyone could tell me if restoring tails from SSMS (only the tail, not the complete backup sequence) is possible, that would be great.[/quote]SSMS doesn't support every possible restore strategy: the built-in feature that stitches together a sequence of restores is limited to the most common requirements.You can still achieve the result via the UI, by performing one step at a time, it's just not as convenient.[/quote]Hi Paul,Not sure what you mean. Just to clarify my question a bit: I have restored everything, except the tail log backup (and I am in no-recovery). If I then start up SSMS and try to restore just the log, I cannot restore, because I cannot "Select the Backup Sets to restore". To me this is one step at a time, but maybe I have still missed something?Thanks!Fri, 30 Apr 2010 03:19:43 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic913383-1323-1.aspx[quote][b]Christian Buettner-167247 (4/30/2010)[/b][hr]Tested it with SSMS and failed ("Select the Backup Sets to restore" shows no entries after selecting the tail backup in "From device:")...If anyone could tell me if restoring tails from SSMS (only the tail, not the complete backup sequence) is possible, that would be great.[/quote]SSMS doesn't support every possible restore strategy: the built-in feature that stitches together a sequence of restores is limited to the most common requirements.You can still achieve the result via the UI, by performing one step at a time, it's just not as convenient.Fri, 30 Apr 2010 03:05:22 GMTPaul White