April 29, 2010 at 10:21 pm
Lynn Pettis (4/29/2010)
Comments posted to this topic are about the item <A HREF="/questions/Backup/69903/">Backups</A>
I think the answer is wrong, in my opinion the right one is:
Restore Tuesday nights full backup with norecovery.
Restore the latest differential backup, Wednesday @ 8:00 AM, with norecovery.
Restore all transaction log backups taken since the latest differential backup restored with norecovery.
Restore the tail-log backup with recovery.
April 29, 2010 at 10:34 pm
The question threw me off. Tuesday @ 4pm is not the 'latest differential backup'. Wednesday 8am is.
April 29, 2010 at 10:52 pm
java56p (4/29/2010)
Lynn Pettis (4/29/2010)
Comments posted to this topic are about the item <A HREF="/questions/Backup/69903/">Backups</A>I think the answer is wrong, in my opinion the right one is:
Restore Tuesday nights full backup with norecovery.
Restore the latest differential backup, Wednesday @ 8:00 AM, with norecovery.
Restore all transaction log backups taken since the latest differential backup restored with norecovery.
Restore the tail-log backup with recovery.
Reread the question, Tuesday nights full backup file is corrupt, how do you recover to 9:00 AM on Wednesday?
April 29, 2010 at 10:53 pm
awoodhouse (4/29/2010)
The question threw me off. Tuesday @ 4pm is not the 'latest differential backup'. Wednesday 8am is.
Reread the question, you are using the Full backp from Monday, what is the latest differential backup?
April 29, 2010 at 11:00 pm
Depends on your point of view. If i were selecting from a list of files on the file system sorted by date created, the most recent one would be Wednesday 8am.
Edit:
Since we are restoring backup files, the file system would be the point of view considered.
April 29, 2010 at 11:06 pm
Lynn Pettis (4/29/2010)
Comments posted to this topic are about the item <A HREF="/questions/Backup/69903/">Backups</A>
Yes You are right, I did not read the question completely. Then my answer is wrong.
April 29, 2010 at 11:26 pm
awoodhouse (4/29/2010)
Depends on your point of view. If i were selecting from a list of files on the file system sorted by date created, the most recent one would be Wednesday 8am.Edit:
Since we are restoring backup files, the file system would be the point of view considered.
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.
April 29, 2010 at 11:41 pm
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.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 30, 2010 at 12:04 am
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.
Actually, the basis of this question was not from a real world failure, but based on several posts here on SSC where people were truncating the t-log just prior to a full backup or simply truncating the t-log as a part of normal processing. I wanted to show why you don't necessarily want to to do things like that.
In this particular case, how would you recover to 9:00 AM Wednesday if you truncated the t-log just before the Tuesday night full backup and later discovered that the backup file was corrupt?
April 30, 2010 at 12:23 am
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
SQLkiwi blog
@SQL_Kiwi
April 30, 2010 at 12:26 am
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[/url]
Learn Extended Events
April 30, 2010 at 12:32 am
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
SQLkiwi blog
@SQL_Kiwi
April 30, 2010 at 1:45 am
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
April 30, 2010 at 1:45 am
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...
Viewing 15 posts - 1 through 15 (of 60 total)
You must be logged in to reply to this topic. Login to reply