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


Backups


Backups

Author
Message
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35348 Visits: 11361
CirquedeSQLeil (4/29/2010)
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.

I don't think we can infer from the question that the hardware failure was responsible for corrupting both the live database and the backup file. It's certainly possible, but I don't think it can be assumed.

It is quite possible to backup the tail of the log even if the database cannot be started - all that is required is that the log is undamaged, there are no bulk logged changes, and the database state supports the operation. So, I don't think we can assume that the database could be "brought back online", either.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)

Group: General Forum Members
Points: 66261 Visits: 18570
Paul White NZ (4/30/2010)
CirquedeSQLeil (4/29/2010)
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.

I don't think we can infer from the question that the hardware failure was responsible for corrupting both the live database and the backup file. It's certainly possible, but I don't think it can be assumed.

It is quite possible to backup the tail of the log even if the database cannot be started - all that is required is that the log is undamaged, there are no bulk logged changes, and the database state supports the operation. So, I don't think we can assume that the database could be "brought back online", either.


Good points and info.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35348 Visits: 11361
awoodhouse (4/29/2010)
The question threw me off. Tuesday @ 4pm is not the 'latest differential backup'. Wednesday 8am is.

I have some sympathy with this view. So many QotDs have tried to be 'clever' that many people are wary of ambiguous wording such as "Restore all transaction log backups taken since the latest differential backup".

I hesitated before choosing the correct answer: I was half-expecting to get this 'wrong' with a smug explanation relying on the ambiguity. That would have been tedious in the extreme, but I have seen worse on QotD.

A good question overall, based on an important concept. Well done, Lynn. I just wish there wasn't that small ambiguity.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5265 Visits: 3889
Nice QOTD!
Tested it with SSMS and failed ("Select the Backup Sets to restore" shows no entries after selecting the tail backup in "From device:")

Tested it with the "standard" SQL method and succeededSmile

USE MASTER
GO
DROP DATABASE BACK
GO
CREATE DATABASE BACK
GO
BACKUP DATABASE BACK TO DISK = 'C:\temp\BACK1Full.bak' WITH NAME='BACK1Full', INIT
CREATE TABLE BACK.dbo.Test (a int)
BACKUP DATABASE [BACK] TO DISK = N'C:\temp\BACK2Diff.bak' WITH DiffERENTIAL , NAME='BACK2Diff' , INIT
INSERT BACK.dbo.Test VALUES (1)
BACKUP DATABASE BACK TO DISK = 'C:\temp\BACK3Full.bak' WITH NAME='BACK3Full' , INIT
INSERT BACK.dbo.Test VALUES (2)
BACKUP Log [BACK] TO DISK = N'C:\temp\BACK4Log.bak' WITH NAME='BACK4Log' , INIT
INSERT BACK.dbo.Test VALUES (3)
BACKUP Log [BACK] TO DISK = N'C:\temp\BACK5Lognorecovery.bak' WITH NO_TRUNCATE , NORECOVERY ,NAME='BACK5Lognorecovery' , INIT
GO

--Start restore sequence from first full backup (ignoring 2nd "corrupt" backup)
RESTORE DATABASE BACK FROM DISK = 'C:\temp\BACK1Full.bak' WITH NORECOVERY
RESTORE DATABASE BACK FROM DISK = 'C:\temp\BACK2Diff.bak' WITH NORECOVERY
RESTORE DATABASE BACK FROM DISK = 'C:\temp\BACK4Log.bak' WITH NORECOVERY

-- How does this work from SSMS?
RESTORE DATABASE BACK FROM DISK = 'C:\temp\BACK5Lognorecovery.bak' WITH RECOVERY
GO
-- Check if all data is still there
SELECT * FROM BACK.dbo.Test



If anyone could tell me if restoring tails from SSMS (only the tail, not the complete backup sequence) is possible, that would be great.

Best Regards,

Chris Büttner
rodjkidd
rodjkidd
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6445 Visits: 8673
Good question Lynn.

Just a pity even after one cup of coffee it was still to early for me to see the bit about Tuesday's full backup being corrupt.

Cheers,

Rodders...



Chris Houghton
Chris Houghton
SSC Eights!
SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)

Group: General Forum Members
Points: 890 Visits: 1710
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
paul.knibbs
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4194 Visits: 6240
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
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18747 Visits: 12426
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17020 Visits: 7413
Practical situation where this did apply.
one of my clients had a simultaneous RAID and SAN failure (hardware).
had the tran log not been truncated for the previous day (as the latest was corrupted as a result of the raid failure), only 2 hours work would have been lost, and not 3 days.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35348 Visits: 11361
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
SQLblog.com
@SQL_Kiwi
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