?
Solved

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

Posted on 2011-03-15
17
Medium Priority
?
620 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

649 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