December 16, 2011 at 3:20 pm
I was reading an article about backup and restore and it did not make 100% sense.
The issue that confused me is :
If I have a "Full Backup" at 7AM
And a "Differential Backup" at 9AM
A "Transaction Log Backup" at 9AM,10AM,11AM
If my server crashes at 9:30AM
I know I need to restore my "Full Backup" and "Differential Backup"
Per the article I should also restore the "Transaction Log Backup" at "9AM" and 10AM.
The 10AM Transaction log makes sense - but why the 9AM Transaction Log. Wouldn't the 9AM Differential Backup take care of the restore?
Thanks
December 16, 2011 at 3:51 pm
Maybe. They're at the same time so, if the diff was fast and the log a little slower (iirc they can run concurrently), it could be that the log finished after the diff and in that case restoring just the 10AM log will give you an error (Can't restore, LSN too recent to apply or something like that)
If you restore the 9AM log and nothing needs to be restored from it, it won't break anything. You'll get an error saying that the restore can't be done, the LSN is too early, then you just restore the 10AM.
It's just been safe. Nothing will break if you restore it and don't need it, you can just ignore the error and carry on with the 10AM log restore.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 16, 2011 at 3:53 pm
That said, if the server crashes at 9:30, all you can restore is the full and the diff (and maybe the 9am log), unless you managed to take a tail-log backup, as the 10AM log backup wouldn't have happened.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 16, 2011 at 4:24 pm
Thanks!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply