December 2, 2011 at 11:34 am
You mean restore the latest differential backup over the secondary DB, right?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 2, 2011 at 11:40 am
Err, no...
I mean there's no point in restoring the last successful log backup WITH RECOVERY, then shrinking the log, then restoring the full backup over that, followed by the last differential and logs.
Restoring a differential over the secondary database as it is now may or maynot work, I really don't know, it's worth a try to see if that will fix the log size. If it doesn't, then just restore the full database over the existing DB and then restore diff and logs.
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 2, 2011 at 12:00 pm
I understand but still seeking clarification on:
If I restore the full backup, then apply the last differentials to the secondary server AND a new full backup occurs on the primary server during that time, won't that shag it all up?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 2, 2011 at 12:22 pm
don't blow the logshipping away and start again. Remove all the log backups on the secondary server. On the primary move them to another directory, i.e you just need to have your logshipping directories empty and you can start again with the same logshipping jobs, it will pick up from the next available log in the directory.
---------------------------------------------------------------------
December 2, 2011 at 12:24 pm
MyDoggieJessie (12/2/2011)
If I restore the full backup, then apply the last differentials to the secondary server AND a new full backup occurs on the primary server during that time, won't that shag it all up?
No, providing the new full backup doesn't occur between the full backup that you're copying and the differential that you're copying it'll be fine.
Full backups do not break the log chain. Ever.
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 2, 2011 at 12:27 pm
MyDoggieJessie (12/2/2011)
I understand but still seeking clarification on:If I restore the full backup, then apply the last differentials to the secondary server AND a new full backup occurs on the primary server during that time, won't that shag it all up?
As long as the full backup you apply on the secondary is the root for the differential you apply you will be fine, i.e no extra full backup between the restored full and differential.
IF you have a differential at the moment who's root is the full backup you used to initiate logshipping last time, its worth a go restoring that with norecovery first, that will save you a lot of time.
---------------------------------------------------------------------
December 2, 2011 at 12:42 pm
Again, it will take 18 hours to copy a full backup up to the our DR location (due to it's size - 88GB compressed via Hyperbac)...during this time a FULL backup, and 2 differential backups will occur...APOLOGIES if I'm just not getting this so...
Should I:
1. Copy up the most recent full backup and all differentials to secondary server
2. Disable future full/differntial backups on the primary server until a point in time when the full/diffs have been successfully restored - TRN log shipping will continue to ship TRN files up tp a folder in the DR data center during this time
3. Restore them in the proper order to the secondary server
4. When/If full/diff backups complete restoring to secondary server, begin restoring TRN files, starting with the one immeidately following the last diff backup. Restore all TRN files that have been logshipped.
5. The secondary DB should now be in standby mode, having all logshipped TRN files fromt he primary server restored to the secondary server
6. Enable the full and diff backups back on the primary server?
Sounds like the steps needed to get this working as a NEW full backup WILL occur by the time the secondary DB has been restored ith the one that is currently being copied up there.
Will the steps I've outlined above work? Apologies if I am not explaining myself properly...there hasn't been much sleep gotten in the past 50 hours...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 2, 2011 at 1:09 pm
Copy over your full backup and LATEST differential ONLY. After that you only need to be sure you have all the log backups after the differential available.
so you can then continue with your diffs and fulls as usual, if you want to be extra careful, make subsequent full backups COPY_ONLY until the secondary is working again.
Once secondary is restored with norecovery, start restoring the log files using the logshipping process to do this for you, so as I said ensure log directory on secondary is empty and log shipping directory on the primary has no log backups BEFORE the differential you restored.
Keep the log shipping copy and restore jobs disabled until you are ready to start running them again.
---------------------------------------------------------------------
December 2, 2011 at 2:39 pm
MyDoggieJessie (12/2/2011)
Again, it will take 18 hours to copy a full backup up to the our DR location (due to it's size - 88GB compressed via Hyperbac)
What's the distance between primary and DR?
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 2, 2011 at 2:51 pm
About 300 miles
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 2, 2011 at 2:56 pm
Ok, that pretty much eliminates the 'copy to external harddrive and drive the backup over' that I was going to suggest.
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 2, 2011 at 3:29 pm
Gail, yes, if that were an option I would have done that before placing this post :hehe:
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 2, 2011 at 3:39 pm
does it? Unless you live in the back of beyond should be able to drive 300 miles in 5 to 6 hours 🙂
---------------------------------------------------------------------
December 3, 2011 at 6:39 pm
I believe this has been resolved. Thanks to everyone who helped with the solution...just another reason why I simply LOVE this website...you guys (and gals) ROCK 😀
I pulled the most recent backup and all the differentials up until the next full backup ran (bearing in mind that due to the size of the files, the time it takes to copy them to our DR data center another full backup has run, and several other differentials...so I can't copy the diff's since that point in time)
I moved all the TRN files that occured BEFORE the last differential backup to a different folder and left the newer ones, then enabled the RESTORE logshipping job...which picked up and began restoring them - after about an hour everything appears to be current...
Certainly hope I didn't miss anything...I assumed that if I didn't something wrong I would have received an error or some sort...
?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 4, 2011 at 3:05 am
glad to hear thats working again.
you say you copied ALL the diffs? Only the latest one would have been needed to be restored.
Presume you enabled the copy job as well? If the jobs are working and the restore is actually loading logs (check that it is) the restore chain must be in sequence so everything is good.
don't forget at some point to delete the log backups you moved 🙂
---------------------------------------------------------------------
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply