October 27, 2009 at 1:58 pm
I am using log shipping to keep a DR site up to date. We occasionally need to test the DR site SQL Server. What is the best way to bring up the DR site, perform the test, and then resume log shipping.
October 27, 2009 at 2:07 pm
you will have to do a restore with recovery on each database. this will allow you to do your testing. Afterwards, you will want to restore your last full backups and apply any logs that have been taken since the last full backup.
October 27, 2009 at 7:05 pm
that is what I am doing now. I was hoping there was a shortcut, some of my databases are 500GB.
October 28, 2009 at 2:31 am
You don't have to do a full restore to revert things back to the way they were...
If you use the system stored procedures sp_change_primary_role and sp_change_secondary_role to switch the roles in the first place, and then use them to reverse the roles when you have finished the DR exercise, then no full database restore is required.
There's a lot more to it, but in summary these procedures actually do the following:
On the primary BACKUP LOG WITH NORECOVERY (or STANDBY)
On the secondary RESTORE LOG WITH RECOVERY
So to reverse the log shipping back to it's original state, do the opposite
On the old secondary BACKUP LOG WITH NORECOVERY (or STANDBY)
On the old primary RESTORE LOG WITH RECOVERY
October 28, 2009 at 4:19 am
thanks, I will give that a try. I will try executing those stored procs from a script.
October 28, 2009 at 7:38 am
remember if you do this any updates done in DR will be carried back to the live site, so be sure that is what you want to happen.
---------------------------------------------------------------------
October 28, 2009 at 8:14 am
Ahhh. Good point. I will ponder that.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply