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

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?
atorexAsked:
Who is Participating?
 
Daniel_PLConnect With a Mentor DB Expert/ArchitectCommented:
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
 
Darran BrownDBACommented:
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
 
Darran BrownDBACommented:
hmmm, just reading my post, sorry for the bad typing.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
atorexAuthor Commented:

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
 
Daniel_PLDB Expert/ArchitectCommented:
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
 
Darran BrownDBACommented:
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
 
Daniel_PLDB Expert/ArchitectCommented:
Please keep in mind that querying secondary database in log shipping prevents it from restore until any activity in this db is off.
0
 
atorexAuthor Commented:
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
 
Daniel_PLDB Expert/ArchitectCommented:
What are you doing in that maintenace? If it's shinking or truncating it leads you to log shipping failure.
0
 
atorexAuthor Commented:
its a log backup with defaults, and I think that truncates.
0
 
Daniel_PLDB Expert/ArchitectCommented:
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
 
atorexAuthor Commented:
OK that makes sense, let me kill that process and see what I get.

Much thanks,
0
 
Daniel_PLDB Expert/ArchitectCommented:
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
 
atorexAuthor Commented:
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
 
Daniel_PLDB Expert/ArchitectCommented:
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
 
atorexAuthor Commented:
So I can do log backups as long as I remove the truncate option out?
0
 
atorexAuthor Commented:
Thanks, DanielP
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.