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


Backups


Backups

Author
Message
Paul White
Paul White
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16094 Visits: 11355
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-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32952 Visits: 18560
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16094 Visits: 11355
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
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3623 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
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4299 Visits: 8416
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
Say Hey Kid
Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)

Group: General Forum Members
Points: 702 Visits: 1697
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
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2601 Visits: 6232
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
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11152 Visits: 12004
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
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9049 Visits: 7283
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16094 Visits: 11355
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