December 5, 2011 at 7:37 am
Hello all,
I would like to disable log shipping on one of our server temporarily when the Dr server is going to move to a different physical location.
i want to find out the best way to go about it,,,, please let me know if you guys have any input.
this is my game plan, would like to know if i am on the right track.
in LS... there are basically three job running.
- Backup job of the database on the primary server.
- copy job on the secondary server
- restore job on the secondary server.
i want to go to sql agent jobs and disable them for the duration of the move and enable them when every thing is backup on line.
since this the first time for me , am I on the right track?
Thanks in advance.
December 5, 2011 at 7:43 am
It sounds OK. Just make sure once you enable jobs they restore the backups in proper sequence (without breaking the chain).
December 5, 2011 at 8:08 am
what do you mean by make sure the chains are not broken...
since this is a automated process.. do i have to enable job in a specific order?
thanks in advance.
December 5, 2011 at 8:15 am
qur7 (12/5/2011)
what do you mean by make sure the chains are not broken...since this is a automated process.. do i have to enable job in a specific order?
thanks in advance.
Yes. Consider a scenario where there is a slight gap in disabling restore & backup jobs. Backup been moved to Secondary Server but didn't restore.
Now, you enable all jobs (with some interval). By this time Secondary Server may have more than 1 backup to restore. It will error out if you don’t restore the backups in proper sequence.
December 5, 2011 at 8:29 am
I would aim to just disable the copy and restore jobs on the DR server.
This is because it is best to keep backing up the logs on the primary for restore purposes and avoid the log filling up.
Just make sure you can get the DR server moved and set up within the time period you have set up for keeping the log backups on the primary before they are deleted.
---------------------------------------------------------------------
December 5, 2011 at 9:23 am
thanks for both of you guys for the input...
if the chain is broken,,, and when i enable the job,, i think i have to do is to re-initialize the backups and apply at the secondary.
what you guys think .
Thanks in advance.
December 5, 2011 at 9:27 am
you would have to start again from a full backup restore.
But there is no reason why the chain should become broken in this scenario as long as all your log backups are available to you on the primary
---------------------------------------------------------------------
December 5, 2011 at 9:34 am
if the chain is broken,,, and when i enable the job,, i think i have to do is to re-initialize the backups and apply at the secondary.
To avoid it, I would recommend you to go through following discussion.
SQL 2008 Logshipping, secondary database issue
http://www.sqlservercentral.com/Forums/Topic1215513-391-1.aspx
December 6, 2011 at 1:55 am
Dev (12/5/2011)
It sounds OK. Just make sure once you enable jobs they restore the backups in proper sequence (without breaking the chain).
Log shipping will automatically restore the logs in sequence if there are more than one.
qur7 (12/5/2011)
do i have to enable job in a specific order?
Disable the jobs and the alert jobs to.
When you have moved the servers i would first enable and start the restore job, once all current log backups have been processed start the backup job and then the copy.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 6, 2011 at 6:51 am
Perry Whittle (12/6/2011)
Dev (12/5/2011)
It sounds OK. Just make sure once you enable jobs they restore the backups in proper sequence (without breaking the chain).Log shipping will automatically restore the logs in sequence if there are more than one.
I don't think so. Are you sure on it? Can you please guide me to supporting document?
The below discussion is on Log Shipping / Log Restore order only.
SQL 2008 Logshipping, secondary database issue
http://www.sqlservercentral.com/Forums/Topic1215513-391-1.aspx
December 6, 2011 at 6:59 am
Dev (12/6/2011)
Perry Whittle (12/6/2011)
Dev (12/5/2011)
It sounds OK. Just make sure once you enable jobs they restore the backups in proper sequence (without breaking the chain).Log shipping will automatically restore the logs in sequence if there are more than one.
I don't think so. Are you sure on it? Can you please guide me to supporting document?
The below discussion is on Log Shipping / Log Restore order only.
SQL 2008 Logshipping, secondary database issue
http://www.sqlservercentral.com/Forums/Topic1215513-391-1.aspx
I was involved in the above thread,. Nowhere does it say logshipping cannot restore more than one log at a time, the OP may have tried to do it manually, but he didn't need to.
Log shipping restores start from the next log after the last one it restored in the previous run, and works through the directory until there are no more left.
---------------------------------------------------------------------
December 6, 2011 at 7:08 am
george sibbald (12/6/2011)
Dev (12/6/2011)
Perry Whittle (12/6/2011)
Dev (12/5/2011)
It sounds OK. Just make sure once you enable jobs they restore the backups in proper sequence (without breaking the chain).Log shipping will automatically restore the logs in sequence if there are more than one.
I don't think so. Are you sure on it? Can you please guide me to supporting document?
The below discussion is on Log Shipping / Log Restore order only.
SQL 2008 Logshipping, secondary database issue
http://www.sqlservercentral.com/Forums/Topic1215513-391-1.aspx
I was involved in the above thread,. Nowhere does it say logshipping cannot restore more than one log at a time, the OP may have tried to do it manually, but he didn't need to.
Log shipping restores start from the next log after the last one it restored in the previous run, and works through the directory until there are no more left.
I am not sure what to say here. I have seen guys messed-up Log Shipping because of bad restore order (or unnecessary recovery).
(Note to self, only manual interventions can mess-up log shipping. It restores in sequence automatically)
December 6, 2011 at 2:28 pm
Dev (12/6/2011)
Perry Whittle (12/6/2011)
Dev (12/5/2011)
It sounds OK. Just make sure once you enable jobs they restore the backups in proper sequence (without breaking the chain).Log shipping will automatically restore the logs in sequence if there are more than one.
I don't think so. Are you sure on it? Can you please guide me to supporting document?
The below discussion is on Log Shipping / Log Restore order only.
SQL 2008 Logshipping, secondary database issue
http://www.sqlservercentral.com/Forums/Topic1215513-391-1.aspx
Dear oh dear, please take the time to understand exactly how log shipping works. Set it up and run it and you'll see how it operates. The following from MSDN quotes
MSDN
On a given secondary server instance, the restore job can be scheduled as frequently as the copy job, or the restore job can delayed. Scheduling these jobs with the same frequency keeps the secondary database as closely aligned with the primary database as possible to create a warm standby database.
The log backups have a specially generated naming convention and SQL Server knows exactly which backup has or has not been restored.
A missing file is the most likely cause of the log ship failure. Whether this is deleted manually by a user or by the Log Shipping Primary cleanup (be careful what you set for the file retention).
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 7, 2011 at 2:40 am
Perry Whittle (12/6/2011)
Dear oh dear, please take the time to understand...
Perry oh Perry, didn’t you read my last post? I have already taken back my words... 😛
December 7, 2011 at 4:05 am
Dude Dev
please accept my apologies 😉
On a side note the other thing that will commonly interrupt the log shipping is a manual transaction log backup that is not part of the LS process, keep an eye for this too.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply