February 25, 2015 at 9:51 am
We have a 1TB Database. Our backup strategy looks like this.
Weekly Full backups - Saturday 10pm. (Takes anywhere between 5 - 9 hours)
twice Nightly Diffs (8:30p and 2:30am - Mid week this takes roughly 1.5 hours)
Hourly Log backups starting at 4:00am until 11pm.
I have an issue where I need to restore to 3:00 today. If my (2:30a)Diff is still running at that recovery point.
I am getting an error when trying to restore Full/2:30a Diff/4a Log with a stopat 3:00a.
Error telling me my log backup is incorrectly formed.
Can I, in fact, restore to this point at all?
Do I need to go back to my previous Diff (8:30p) and restore the logs, if so, which ones?
My nighttime Diff ran from 8:30 - 10p.
I have logs at 9p, 10p, 11p, 4a
Or am I dead in the water?
TIA,
Laura
February 25, 2015 at 10:35 am
You need the last differential which *finished* before the time you want to stop at, the full backup that it is based off and all log backups since the differential used.
What's the exact error you're getting?
If you're trying to use the wrong backup, it'll be something like 'database not restored to the correct point' or 'log too early/late to be restored'.
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
February 25, 2015 at 11:24 am
If it's saying your log backup is incorrectly formed, it might be corrupted. Try doing the restore without a stopat as a test. Oh, and not on the production box of course.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 25, 2015 at 11:36 am
Thank you, both.
I am using LiteSpeed with TSM and the error is:
Msg 60000, Level 16, State 1, Line 0
An error was received while reading backup file- Cancelling
RESTORE LOG is terminating abnormally.
The media family on device LiteSpeed for SQL Server backup file is incorrectly formed. SQL Server cannot process this media family.
I have seen this before when applying incorrect log backups, so it does make sense that I just needed to go back one DIFF and apply all the logs. I have yet to try this since don't have the bandwidth to restore another 1TB DB and this one that I was attempting, I recovered released to the business after the error as 2:30am was 'close enough' for them. (Non Prod, of course)
Similarly, If I were to want to restore to a point in time during the Full Backup, would I have the same flexibility? Restore the previous week's FULL, the most recent DIFF and all the LOGs to get to the point in time needed? I know I should just do this to figure it out myself, but I need a database that takes long enough to try this with and most of those are quite large, so I don't have the space to restore to. Vicious cycle.
February 25, 2015 at 11:45 am
You keep saying "restore during".
So, point of clarification for me. Do you mean you're going to try to restore the same database that a backup is being run against? Because you can't do that. If you mean that you want to run a restore using a backup (diff or full) file that is actively being backed up, you can't do that either.
Do you mean by "during" covering a particular time range that would include the time while the backup is running? If so, no, Gail has it right. You need to go back to the previous backup (full or diff), but, you'll want to use the log backup from after the full or diff is completed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 25, 2015 at 11:50 am
As a bit of a sidebar, I can't figure out why anyone would allow a DIF backup to continue during a PIT Restore.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 25, 2015 at 11:52 am
Yes, I can see where that confusion lies. My apologies. I am trying to restore to a point in time that happens to be in the middle of a backup that was taken hours ago.
DIFF runs at 8:30pm
A second DIFF runs from 2:30a-3:55am.
LOG runs at 4am.
At 11:00am, the business requested a restore to 3am.
I applied the latest FULL (from the weekend), and the 2:30am DIFF and the 4am LOG.
Now I see that I needed to go back to the 8:30pm DIFF and apply all the logs up to and including the 4am log.
Hope this clarifies my situation.
Again, thank you so much for the responses.
February 25, 2015 at 12:15 pm
It does, thanks. And Gail nailed it. You need to go the previous one.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 26, 2015 at 7:23 am
Wanted to thank everybody again. This refresh worked perfectly last night.
Restored FULL Backup
Restored 8:30pm DIFF
Restored each log after the DIFF completed and VOILA! was able to restore to a 3:00am PIT.
Thank you all very much! This has been an enlightening experience.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply