Restore

Question

Restore

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

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

webrunner SSC-Dedicated Points: 31863 More actions · Answer 2

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

sergey.gigoyan Ten Centuries Points: 1213 More actions · Answer 3

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.

sestell1 SSChampion Points: 10230 More actions · Answer 4

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.

sergey.gigoyan Ten Centuries Points: 1213 More actions · Answer 5

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.

GeorgeCopeland SSCertifiable Points: 6997 More actions · Answer 6

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?

sestell1 SSChampion Points: 10230 More actions · Answer 7

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.

salvador.sandoval.flores 20433 Newbie Points: 7 More actions · Answer 8

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

doug.davidson SSCommitted Points: 1662 More actions · Answer 9

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.

sergey.gigoyan Ten Centuries Points: 1213 More actions · Answer 10

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.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 11

akljfhnlaflkj

SSC Guru

Points: 76202

October 1, 2015 at 6:52 am

#1830736

Thanks Sergey for the question.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 12

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.