Restore

  • sergey.gigoyan

    Ten Centuries

    Points: 1175

    Comments posted to this topic are about the item Restore

  • This was removed by the editor as SPAM

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    None of these answers are fully correct. To ensure minimal data loss, one should always start by a tail-log backup. (Which will fail if the log file is unavailable, but succeeds if only the data files are lost).


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • helenlu7 65408

    SSC-Addicted

    Points: 425

    Theoretically you can use all the log backs after the full backup. But use Diff backup is far more efficient than using a series of log backups.

    After all, that's is what diff backup is for.

    MCSE Data Platform; BI
    MCITP Database Developer; BI

  • helenlu7 65408

    SSC-Addicted

    Points: 425

    I agree, tail log back is the very first step to take right after the DB crashing, but ONLY IF the log file was still intact.... ie the disk for the log file can still be saved..... depending on the disk RAID level configuration........

    MCSE Data Platform; BI
    MCITP Database Developer; BI

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    helenlu7 65408 (9/30/2015)


    Theoretically you can use all the log backs after the full backup. But use Diff backup is far more efficient than using a series of log backups.

    After all, that's is what diff backup is for.

    With a normal backup schedule, you would be right. But in the case of this question, differentials and log backups are made with the same frequency, so it is the same amount of work (as in, amount of statements to type and execute; I don't know which type of restore performs faster).

    helenlu7 65408 (9/30/2015)


    I agree, tail log back is the very first step to take right after the DB crashing, but ONLY IF the log file was still intact.... ie the disk for the log file can still be saved..... depending on the disk RAID level configuration........

    Absolutely true. But since the question didn't specify the reason of the crash, I was hoping to see an answer option: "attempt to create a tail-log backup".

    If you know the cause of the problem, then you don't attempt - you either do, or you don't because the log is not available anymore.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Iulian -207023

    SSCertifiable

    Points: 7508

    Thumb up, good spot

    Iulian

  • Hany Helmy

    SSChampion

    Points: 13435

    Hugo Kornelis (9/30/2015)


    None of these answers are fully correct. To ensure minimal data loss, one should always start by a tail-log backup. (Which will fail if the log file is unavailable, but succeeds if only the data files are lost).

    +1

  • helenlu7 65408

    SSC-Addicted

    Points: 425

    Log back is sequential operations eg one statement at the time.....

    Whereas Diff backup is at data page level, so the later is faster than the former for the same outcome....

    MCSE Data Platform; BI
    MCITP Database Developer; BI

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    Seems like a certification question 😀

    Didn't know you could switch order, so I learned something.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    Koen Verbeeck (9/30/2015)


    Seems like a certification question 😀

    Didn't know you could switch order, so I learned something.

    You can't switch order. All the correct options restore the backups in proper sequence.

    In a "normal" backup schema, the same alternative method can be used but it will be more work.

    Example: Weekly full backup (Sun 2:00 AM). Daily differential (Mon-Sat 2:00 AM). Hourly log backup (every full hour).

    To restore up to Friday 3:15 PM, you would first do the last full, the then Fri 2:00 AM differential, then all hourlies in sequence and finally the tail-log backup.

    But if the Friday 2:00 AM differential turns out to be unusable (media failure), you can instead do full - Thu 2:00 AM diff - all log backups since. That will be a full 24 extra log backups to restore, so more work and more time required - but at least you have your data.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • GeorgeCopeland

    SSCertifiable

    Points: 6896

    Why on earth would it be ok to restore a txn log when you have a diff???

  • This was removed by the editor as SPAM

  • Ken Wymore

    SSCoach

    Points: 16429

    Nice practical example. Thanks Steve.

  • GeorgeCopeland

    SSCertifiable

    Points: 6896

    Stewart "Arturius" Campbell (9/30/2015)


    As an example, the last diff might be faulty / corrupted

    I don't understand your example. If a diff is faulty then you don't have it.

Viewing 15 posts - 1 through 15 (of 27 total)

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