September 29, 2015 at 11:27 pm
This was removed by the editor as SPAM
September 30, 2015 at 12:17 am
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).
September 30, 2015 at 12:24 am
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
September 30, 2015 at 12:29 am
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
September 30, 2015 at 12:53 am
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.
September 30, 2015 at 12:56 am
Thumb up, good spot
Iulian
September 30, 2015 at 1:47 am
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
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
September 30, 2015 at 2:16 am
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
September 30, 2015 at 3:53 am
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
September 30, 2015 at 4:06 am
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.
September 30, 2015 at 7:19 am
Why on earth would it be ok to restore a txn log when you have a diff???
September 30, 2015 at 7:40 am
This was removed by the editor as SPAM
September 30, 2015 at 7:47 am
Nice practical example. Thanks Steve.
September 30, 2015 at 7:58 am
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 26 total)
You must be logged in to reply to this topic. Login to reply