?
Solved

SQL 2008 changing a log ship partner from restore state to normal

Posted on 2011-03-15
17
Medium Priority
?
617 Views
Last Modified: 2012-05-11
I have a log ship 2008 server, the target is fully recovered but it is in restoring mode. I need to use this server for reporting I would like to set it to normal state so I can make a user edit and then place it in standby, so how do I do the first step change the state from restoring to normal.
I'm assuming the second step would be to start the logship again?
0
Comment
Question by:atorex
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 3
17 Comments
 
LVL 2

Expert Comment

by:Darran Brown
ID: 35136874
Hi

Your database should be in Standy/read only mode if it's being log shipped. You can use the database as read only but can't change anything in it.
If the database is in restorig mode then it's either in the middle of restoring the a transaction log or it's failed to restore.
If it was in standby state then you can make the database fully operational but to change it back to standby you would need to restore a full database backup with the 'restore with standby' option and start log shipping again.
You will also have to perfrom the full restore to recover from restore mode if there is no restore currently running.

Hope this helps, but post back if no joy.
0
 
LVL 2

Expert Comment

by:Darran Brown
ID: 35136887
hmmm, just reading my post, sorry for the bad typing.
0
 

Author Comment

by:atorex
ID: 35137514

nothing wrong with your post, as my typing is suspect at best!!

The restore mode is due to the logship was setup to restore with recovery not with standby, I was hoping to execute something like
ALTER DATABASE [Works] SET STANDBY, I know that does not work but was hoping someone starter then I would know the process and this is the site for such person (a million of you)

so is there no way other then re configuring log shipping to restore with standby?
0
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35138080
You definitely are able to set up database to standby without reinitializing, just change restore settings.
Right click your database which is log shipped  -> Tasks... -> Ship transaction logs -> then click three dots button -> switch to Restore Transaction Log -> choose standby mode
0
 
LVL 2

Expert Comment

by:Darran Brown
ID: 35138206
Hi,
Yep, sorry thought it was being shipped in standy by already.  As above, you basically want to change the next transaction logs be restored in standy mode. Once you're ready to go back(if needed), amend back to restore norecovery mode.  All the logs are being restored as normal just in different modes so no need interupt the logshipping.  I think the above post from Daniel_PL is what your looking for so if it works and your happy, send the points to Daniel.

Good luck
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35138228
Please keep in mind that querying secondary database in log shipping prevents it from restore until any activity in this db is off.
0
 

Author Comment

by:atorex
ID: 35138574
Thanks Guys, I looked for that setting and couldn't figure out the 3 dots, what an idiot!!!
I have noticed that after the maintenance backup procedure I'm getting 14421 error in the log and no longer restoring the last restore was at 3:05 AM the backup was done at 3:15 log ship is running on the hour 1 hour intervals. it is now 9:44 AM the logs have been shipped but will not restore as being out of sequence. DO I NEED TO STOP LOG MAINTENANCE FOR LOG SHIP TO RUN CORRECTLY?
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35138645
What are you doing in that maintenace? If it's shinking or truncating it leads you to log shipping failure.
0
 

Author Comment

by:atorex
ID: 35138692
its a log backup with defaults, and I think that truncates.
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35138796
If it's log backup with defaults it isn't truncating. If you backup that log outside of log shipping it breaks log chain number and your log shipping isn't able to continue.
You don't need to backup log while it is already in backup - by log shipping :)
0
 

Author Comment

by:atorex
ID: 35139093
OK that makes sense, let me kill that process and see what I get.

Much thanks,
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35139231
If your log shipping is broken now you need to reinitialize it again.
What do you want to get by that additional log backup in nightly maintenance?
0
 

Author Comment

by:atorex
ID: 35139341
That was the initial backup to keep the log size down, this was active before logshipping was done.
The source database is also the principal in a mirror to a second server, the new database with the log ship is to be used for reporting purposes only. With the full restore model the log was getting very large and needed to be truncated 5 times per day to keep it within what we wanted.
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35139431
Truncation will blow your log shipping, additionally it creates file fragmentation. It's not good idea to shrink.
You can instead increase log backup frequency to faster remove inactive transactions from log.
0
 

Author Comment

by:atorex
ID: 35140336
So I can do log backups as long as I remove the truncate option out?
0
 
LVL 14

Accepted Solution

by:
Daniel_PL earned 2000 total points
ID: 35148573
If you take log backup aside of log shipping next log taken by it will not fit to the last one.
I really don't think you need taking additional log backups for databases participating in log shipping, if you want to you can increase log shipping backup and copy jobs frequency.
It may bring you lower backup sizes and inactive transactions will be removed faster from tlog.

0
 

Author Closing Comment

by:atorex
ID: 35176183
Thanks, DanielP
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question