<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server 2005 General Discussion
»
Testing Secondary database in Log Shipping
13 posts, Page 1 of 2
1
2
»»
Testing Secondary database in Log Shipping
Rate Topic
Display Mode
Topic Options
Author
Message
alark
alark
Posted Monday, March 26, 2012 6:01 PM
SSC Rookie
Group: General Forum Members
Last Login: Yesterday @ 4:41 PM
Points: 40,
Visits: 224
Our customer wants to test all secondary databases in our Log Shipping environment. Since the test require writes/updates, I will need to change databases from StandBy to Online mode. As I understand correctly, it will break the log shipping.
Is there a nice way to conduct the test without breaking log shipping? If not, then how should I proceed to have the least amount of work to do later? Or there is no way and I will need to build log shipping from scratch again?
Post #1273113
Ian Scarlett
Ian Scarlett
Posted Tuesday, March 27, 2012 1:39 AM
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 2:12 AM
Points: 1,322,
Visits: 4,400
You won't break log shipping... have a look in BOL at Log Shipping Role Changes.
Essentially, you do your final transaction log backup at the primary using "WITH NORECOVERY", then
restore this backup at the secondary using "WITH RECOVERY"
Your secondary is now acting as the primary, so all the backing up and restoring of logs is reversed. When you are ready for the primary to assume it's original role just do the reverse (backing up the secondary WITH NORECOVERY etc).
Post #1273247
GilaMonster
GilaMonster
Posted Tuesday, March 27, 2012 2:15 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744,
Visits: 30,025
Since you're bringing the secondary DB online and making changes, there's no 'go back' after that. Take a full backup from primary, restore on the secondary WITH NORECOVERY and the log shipping should continue.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1273271
Ian Scarlett
Ian Scarlett
Posted Tuesday, March 27, 2012 2:26 AM
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 2:12 AM
Points: 1,322,
Visits: 4,400
GilaMonster (3/27/2012)
Since you're bringing the secondary DB online and making changes, there's no 'go back' after that.
Are you sure? This was something I had done previously (SQL2000 admittedly) with a customised log shipping solution.
Backing up the last transaction log at the primary using WITH NORECOVERY and then restoring that on the secondary keeps the LSNs in sync. That allows you to you to start backing up at the secondary and restoring them at the primary.
Post #1273281
GilaMonster
GilaMonster
Posted Tuesday, March 27, 2012 2:45 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744,
Visits: 30,025
He's making changes to the secondary. Hence the LSNs won't be in sync after that. I don't read that as asking how to switch log shipping around, just how to test the secondary without breaking the log shipping to it (and that can't be done)
Restoring a log WITH RECOVERY will bring the DB online all right, nothing wrong there. If changes are made to the secondary, it can't be just backed up WITH NORECOVERY afterwards and have the log backups from the primary continue to be restored.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1273288
Ian Scarlett
Ian Scarlett
Posted Tuesday, March 27, 2012 2:57 AM
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 2:12 AM
Points: 1,322,
Visits: 4,400
Agreed... it looks like our reading of the original requirement is different.
I assumed the test would involve a role switch and back again, but with some updates done at the secondary in between.
Doing a test to simply bring the secondary databases online doesn't prove much.
Post #1273296
alark
alark
Posted Tuesday, March 27, 2012 8:46 AM
SSC Rookie
Group: General Forum Members
Last Login: Yesterday @ 4:41 PM
Points: 40,
Visits: 224
GilaMonster, You heard me right. I will bring the secondary databases for testing the apps in DR environment, meanwhile primary databases will continue running in real production. Then after the testing completes, I need to restore log shipping. So my question is about the steps: Will I have to rebuild the log shipping for all DR databases from the scratch or I can just disable the existing LS jobs, restore full backups, then enable LS jobs?
Post #1273551
GilaMonster
GilaMonster
Posted Tuesday, March 27, 2012 8:47 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744,
Visits: 30,025
I suspect the latter, but please do test that out on a dev/test machine first.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1273553
TheSQLGuru
TheSQLGuru
Posted Tuesday, March 27, 2012 10:05 PM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 6:52 PM
Points: 3,582,
Visits: 5,132
alark (3/27/2012)
GilaMonster, You heard me right. I will bring the secondary databases for testing the apps in DR environment, meanwhile primary databases will continue running in real production. Then after the testing completes, I need to restore log shipping. So my question is about the steps: Will I have to rebuild the log shipping for all DR databases from the scratch or I can just disable the existing LS jobs, restore full backups, then enable LS jobs?
You will not be able to restore any additional tlogs from the still-operational primary after you open up the secondary databases for writes (i.e. restore a tlog with recovery). Time to reinitialize I am afraid.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1274039
DBA_Dom
DBA_Dom
Posted Friday, March 30, 2012 1:48 PM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Today @ 10:28 PM
Points: 563,
Visits: 59,146
You will need to rebuild log shipping from scratch. This is because of the .tuf file needs to be recreated and can only be done by sql server itself. Believe me I have tried to recreate this file after accidentally deleting it and the only solution is to rebuild log shipping from scratch.
Post #1276007
« Prev Topic
|
Next Topic »
13 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
