Backups

  • Lynn Pettis

    SSC Guru

    Points: 442235

    Comments posted to this topic are about the item Backups

  • java56p

    Say Hey Kid

    Points: 712

    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.

  • awoodhouse

    SSC-Addicted

    Points: 402

    The question threw me off. Tuesday @ 4pm is not the 'latest differential backup'. Wednesday 8am is.

  • Lynn Pettis

    SSC Guru

    Points: 442235

    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?

  • Lynn Pettis

    SSC Guru

    Points: 442235

    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?

  • awoodhouse

    SSC-Addicted

    Points: 402

    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.

  • java56p

    Say Hey Kid

    Points: 712

    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.

  • Lynn Pettis

    SSC Guru

    Points: 442235

    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.

  • SQLRNNR

    SSC Guru

    Points: 281243

    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

  • Lynn Pettis

    SSC Guru

    Points: 442235

    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?

  • Paul White

    SSC Guru

    Points: 150442

    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.

  • SQLRNNR

    SSC Guru

    Points: 281243

    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

  • Paul White

    SSC Guru

    Points: 150442

    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.

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    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

  • rodjkidd

    SSCoach

    Points: 15826

    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 61 total)

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