February 16, 2011 at 2:01 am
Am taking a look at using LS, have set up a basic system, but have a few queries I cannot find answers to.
Can I take a full Back Uo of the Pimary Database, during Log Shipping?
Does this affect the shipping and restore of the next Log file?
Seems that I must keep the Secondary Databse in No Recovery Mode to enable subsequent Logs to be restored. therefore cannot check that the data is atually being restored?
Am doing lots of tsting, but thoughts on these topics would be helpful.
Thank you
Coln
February 16, 2011 at 2:07 am
Can I take a full Back Uo of the Pimary Database, during Log Shipping?
You can take full backup on primary anytime but if you restoring the log backup in secondary then you need to restore this latest full backup else your log restore will fail. OR you can opt for copy_only backup if you dont want to disturb your secondary server restoration sequence.
Does this affect the shipping and restore of the next Log file?
yes as mentioned above.
Seems that I must keep the Secondary Databse in No Recovery Mode to enable subsequent Logs to be restored. therefore cannot check that the data is atually being restored?
no, you can restore with standby option and then you can readyour restored data.
----------
Ashish
February 16, 2011 at 2:21 am
Thanks.
What are the implications (for restore) in using the Copy_Only Back Up method?
Have tried the StandBy mode, but found that subsequent Log restores failed. Perhaps more testing?
February 16, 2011 at 2:37 am
What are the implications (for restore) in using the Copy_Only Back Up method?
as you can read here http://msdn.microsoft.com/en-us/library/ms191495.aspx, copy_only doesnt break your backup chain. So this option you can use when you need the backup copy without affecting the restoration chain.
Have tried the StandBy mode, but found that subsequent Log restores failed. Perhaps more testing?
No, It will not fail. Full syntax for restoring with standby option, you can read/watch here http://sqlserverpedia.com/wiki/Restore_With_Standby
edit---
the only time it will fail when you will not restore the backup in same sequence as it was taken on primary server
----------
Ashish
February 16, 2011 at 3:06 am
Thanks very much.
Just one more query, for the moment.
When setting up LS there were 2 boxes called "Network Path to Back Up Folder" and "Destination folder for copied files". The first is straight forward, the second seems to be a mstery. However, I put file paths in both boxes, just to be sure. TRN files duly appeared in both drectories until the last LS when nothing appeared in the copy directory, until some time later . Any thoughts?
Thanks again, this is very helpful
Colin
February 16, 2011 at 3:14 am
When setting up LS there were 2 boxes called "Network Path to Back Up Folder" and "Destination folder for copied files". The first is straight forward, the second seems to be a mstery.
LS works as per below steps :-
1) You take the full backup on primary server and save it to some local dirve of Primary server(which you referred as network path to backup folder).
2) copy the backup file from primary server to destination server which is your secondary server and backup file copied to some local disk of secondary server ( which you mentioned as Destination folder for copied files)
3) you restore the database with standby/norecovery on secondary sql server.
4) you take the full/differential/log backup on primary and starts repeating the process from step1 to step 3.
However, I put file paths in both boxes, just to be sure. TRN files duly appeared in both drectories until the last LS when nothing appeared in the copy directory, until some time later . Any thoughts?
sorry not able to understand. If you clearify what you saying, I will try to answer as per my knowledge.
----------
Ashish
February 16, 2011 at 4:27 am
I put the same file path in both boxes, expecting the .trn file to appear in both directories at the same time.
This was not always the case. The Log Shipping happens at 10:00 and . trn file appears in "Network Path to Back Up Folder", but NOT in "Destination for copied Files". The next LS takes place and the 10:15 file appears in "Network...." at this time the 10:00 file now appears in the ".....Copied files" directory.
February 16, 2011 at 4:36 am
yes it will not refelct immediately as soon it reflect on your primary server.
As i mentioned the steps in my previous post, the backup file first get placed in local(primary) server and then get copied to seconadry server. So the time it takes to copy is the time difference between primary and secondary
----------
Ashish
February 16, 2011 at 5:25 am
I now have a much better understanding of the system and have managed some successful testing.
Many thanks for your help and support, greatly appreciated.
Colin
February 16, 2011 at 5:28 am
glad to hear your effort worked.
----------
Ashish
February 16, 2011 at 8:50 am
One last thought!
Have you encountered issues with the .ldf file growing very large? A normal full Back Up has the effect of clearing out the .ldf and we will not be using that in Log Shipping.
Thanks again
Colin
February 16, 2011 at 9:00 am
if you asking about general overview on log file growth then I would recommend you to go through this great article from Gail :-
http://qa.sqlservercentral.com/articles/64582/
If your question about managing in your log shipping while doing restoration on secondary then I will advice, you first analyse how frequently you want your secondary to be updated.
If you configuring LS for failover situation then there are other technologies as well which are having more benefit on top of LS like....mirroring, replication, and clustering.
If your LS is just for read only purpose then you can restore a full day backup and then twice in a day differential backup at 11AM and 3PM(if your full backup is getting restored at midnight).
So its totally upto you for what purpose you configuring LS.
----------
Ashish
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply