Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Backups Expand / Collapse
Author
Message
Posted Friday, April 30, 2010 12:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:59 AM
Points: 11,168, Visits: 10,931
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #913459
Posted Friday, April 30, 2010 12:26 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:42 AM
Points: 20,460, Visits: 14,086
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #913462
Posted Friday, April 30, 2010 12:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:59 AM
Points: 11,168, Visits: 10,931
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #913468
Posted Friday, April 30, 2010 1:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:19 AM
Points: 2,814, Visits: 3,851
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 succeeded:)

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
Post #913502
Posted Friday, April 30, 2010 1:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 11:06 AM
Points: 2,847, Visits: 4,836
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...



Post #913503
Posted Friday, April 30, 2010 1:55 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, March 17, 2014 11:04 AM
Points: 558, Visits: 1,454
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.
Post #913507
Posted Friday, April 30, 2010 2:00 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 6:05 AM
Points: 1,528, Visits: 5,172
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!
Post #913510
Posted Friday, April 30, 2010 2:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 5,794, Visits: 8,008
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
Post #913518
Posted Friday, April 30, 2010 3:05 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:40 AM
Points: 3,683, Visits: 4,818
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”
Post #913526
Posted Friday, April 30, 2010 3:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:59 AM
Points: 11,168, Visits: 10,931
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #913528
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse