Backups

Question

Backups

April 30, 2010 at 3:05 am

This was removed by the editor as SPAM

Viewing 15 posts - 16 through 30 (of 60 total)

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

Chris Houghton SSCrazy Points: 2557 More actions · Answer 1

Excellent question. I can't see any issues with the wording - latest differential backup, to me at least, means the latest usable differential backup, ie the one which doesn't have a corrupt differential base.

paul.knibbs SSCoach Points: 15320 More actions · Answer 2

I misread the question too...just re-read it and realised it meant the last differential before the corrupt backup, not the last one taken. D'oh!

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 3

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.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Paul White SSC Guru Points: 150467 More actions · Answer 4

Christian Buettner-167247 (4/30/2010)
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.

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.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 5

Paul White NZ (4/30/2010)
Christian Buettner-167247 (4/30/2010)
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.
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.

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!

Best Regards,

Chris Büttner

Paul White SSC Guru Points: 150467 More actions · Answer 6

Christian Buettner-167247 (4/30/2010)
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!

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')

OR

BACK : Tasks -> Restore -> Transaction Log

You might have to refresh the status of the database in Object Explorer to get the Transaction Log restore option to be available...

Paul

edit: to fix images

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 7

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.

Maybe this is related to my version of SQL Server?

Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)

Thanks!

Best Regards,

Chris Büttner

Paul White SSC Guru Points: 150467 More actions · Answer 8

Christian Buettner-167247 (4/30/2010)
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.

😎

Maybe this is related to my version of SQL Server (9.00.4053.00)?

Possibly. My versions:

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 🙁

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

WayneS SSC Guru Points: 95460 More actions · Answer 9

Hugo Kornelis (4/30/2010)
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.

This hit me also... I had to carefully reread ~~this~~ the question to realize why I got it wrong. 🙁

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Mike McIver SSC-Addicted Points: 493 More actions · Answer 10

Wouldn'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. ((?))

Mike
MacroTrenz.Com
LinkedIn

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 11

Mike McIver (4/30/2010)
Wouldn't the log chain be broken with the Tues 6p full?

No. A full backup does not break the log chain.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

anilkullam Ten Centuries Points: 1042 More actions · Answer 12

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

waiting for potential explanation..

thank you,

[/font]

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 13

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.

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

anilkullam (4/30/2010)
[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.com
waiting for potential explanation..
thank you,
[/font]

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