Restore

  • Comments posted to this topic are about the item Restore

  • This was removed by the editor as SPAM

  • 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/

  • 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

  • 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

  • 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/

  • Thumb up, good spot

    Iulian

  • 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

  • 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

  • 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

  • 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/

  • 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

  • Nice practical example. Thanks Steve.

  • 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