Solved

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

Posted on 2011-03-15
17
607 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:Mr_B
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:Mr_B
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:Mr_B
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

747 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

13 Experts available now in Live!

Get 1:1 Help Now