Restore

  • 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).

    Hugo beat me to this. None of these answers is correct to meet the question requirements. The tail log backup should be taken first.

    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

  • Great question for learning and discussion. I also thought about the tail-log backup point that Hugo raised. But I figured since it wasn't mentioned at all that it was intentionally left out as common to all scenarios. But Hugo was right to point it out.

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • I agree with Hugo and Jason, to restore database with minimal data loss we should firstly try to do tail-log backup.

    However it's mentioned in the question that "it should be restored with minimal data loss using existing files". It is assumed, that we have only these files for restoring. This question aims to illustrate that differential backups don't broke log backup chain and after restoring the full backup 'WITH NORECOVERY' we can continue restoring database in both ways: using differential backup or using only transaction log backups taken after the last full backup.

  • GeorgeCopeland (9/30/2015)


    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.

    Transaction log backups are not affected by differential backups. Each transaction log backup goes back to the previous transaction log back up, all the way back to the first full backup. Because of this, you are able to restore your database with the full backup and then each transaction log backup, skipping the differential backup taken in-between.

  • SQLRNNR (9/30/2015)


    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).

    Hugo beat me to this. None of these answers is correct to meet the question requirements. The tail log backup should be taken first.

    I agree, to restore database with minimal data loss we should firstly try to do tail-log backup.

    However it's mentioned in the question that "it should be restored with minimal data loss using existing files". It is assumed, that we have only these files for restoring. This question aims to illustrate that differential backups don't broke log backup chain and after restoring the full backup 'WITH NORECOVERY' we can continue restoring database in both ways: using differential backup or using only transaction log backups taken after the last full backup.

  • sestell1 (9/30/2015)


    Transaction log backups are not affected by differential backups.<snip>

    What you are saying is obvious, I understand it can be done, I still don't understand why you would want to do it. If I saw someone restoring txn logs when they have an available diff, I would ask them why they are doing it. Why would you ever want to do this???

    I guess another way to ask my question is, if you have txn logs, why bother with a diff?

  • GeorgeCopeland (9/30/2015)


    I guess another way to ask my question is, if you have txn logs, why bother with a diff?

    In this case, there's not much difference. If however you run full backups once a week, diffs each day, and log backups every half-hour, then the diff backup would save you applying all of the transaction logs between the last full backup and the diff backup one at a time.

  • If you have more than 1 option to resolve the issues, you have to choose the option that will be executed more faster;/

  • Just my 2 cents.

    database crashed and it should be restored with minimal data loss using existing files

    Option 3 is faster, you are losing data every second after the crash too; until the db restore is complete.

    Therefore, minimal data loss should remove option 2 from being acceptable.

  • doug.davidson (9/30/2015)


    Just my 2 cents.

    database crashed and it should be restored with minimal data loss using existing files

    Option 3 is faster, you are losing data every second after the crash too; until the db restore is complete.

    Therefore, minimal data loss should remove option 2 from being acceptable.

    I mean minimal data loss and not minimal restoring time. Assumed that database is not acceptable after crashing, and in this question I want to emphasize not faster or slower restoring, but the correct restore sequence.

  • Thanks Sergey for the question.

  • If there is already a mentioned option to have a Diff backup then no point someone will be interested to restore all subsequent Tlog backups after the full bkp!

    Thanks.

Viewing 12 posts - 16 through 26 (of 26 total)

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