Solved

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

Posted on 2011-03-15
17
609 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
  • 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
 
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Viewers will learn how the fundamental information of how to create a table.
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.

867 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now