March 26, 2012 at 6:01 pm
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?
March 27, 2012 at 1:39 am
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).
March 27, 2012 at 2:15 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 27, 2012 at 2:26 am
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.
March 27, 2012 at 2:45 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 27, 2012 at 2:57 am
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.
March 27, 2012 at 8:46 am
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?
March 27, 2012 at 8:47 am
I suspect the latter, but please do test that out on a dev/test machine first.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 27, 2012 at 10:05 pm
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 on googles mail service
March 30, 2012 at 1:48 pm
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.
March 30, 2012 at 3:06 pm
Hi,
Not necessarily, u need to re-build the log shipping right from scratch. AS per ur requiement, u need to bring the secondary database online for testing purpose.
Couple of options
Option 1:
Switch their roles, which is not desired i guess as per the replies or ur req.
Option 2:
1:Make sure both the databases are in sync(check the metadata in msdb).
2:Once u r done with that, u can disable the 3 jobs (backup, copy and restore)
3: Take the full backup of the primary database followed by an immediate tlog backup.
4: Bring the secondary database online. (Restore database <databasename> with recovery)
5: Once the testing is done. restore the full backup with no recovery and restore the "tlog backup with Standy" which creates the TUF file in the path u have specified.
6: U can now enable the 3 jobs and run it to make sure dbs are in sync.
Hoping that ur database is small in size.
If not then i would prefer doing a failover and failback.
I recommend to do this in lower environments first before doing in production.
Good Luck!!!
March 30, 2012 at 3:44 pm
Thank you all! Will see next week which solution will work.
October 30, 2012 at 3:52 am
http://www.sqlservercentral.com/Forums/Topic1332796-1549-1.aspx#bm1344627
hai iam a starter in Dba but want post this which might help u
it might be use ful to you if u want to go for testing in the secondary sever with out breaking this LSN chain as our Dba experts suggested it is very nice if u want to know it in simple like in daily basis casual word check this url i strugled and made this.
[with norecovery means tail log backup.]it is sme wt confusing inthis technical words in sql server.If we do this individually in all modules then we will get clear idea ..iam a daily follower to this site mostly is learned and solved issues through and
investigating to do new things which i really dont know
Thanks
Naga.Rohitkumar
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply