June 1, 2010 at 12:04 pm
I accidentally posted this topic in the 2008 forum, so I apologize for posting it twice....
I have a SQL Server 2005 instance (I'll call Principal) mirroring to one location (Target1) that I want to move to a different location (Target2). The data files are too large to backup Principal and copy over to Target2, but Target1 and Target2 are in the same network and transfers between them are easy. Is there any way to stop the mirror going to Target1 and just move the data files over to Target2 and somehow reestablish the mirror? Everything I've tried so far has failed.
Thanks for any help.
June 1, 2010 at 1:01 pm
What have you tried so far?
I have not tested the following but I think it should work.
Stop connections to the primary.
If mirroring not already in synchronous mode switch to that mode.
Make sure databases are synchronised.
Break mirroring (alter database dbname set partner off)
bring target online and back it up
Copy that backup to target2 and restore with norecovery
take a log backup of primary
Copy to target2 and restore with norecovery
set up mirroring to target2
I STRONGLY suggest setting up a small test database to test this process on. Post back on how you get on.
---------------------------------------------------------------------
June 1, 2010 at 2:22 pm
I have tried as you suggest but ran into a few problems. First off, when I broke the mirror, the target stays in restoring mode and any attempt to get it OUT of that mode was unsuccessful. I found how you can do a FORCE failover, but that refused to work, I'm guessing because it could still see the Principal? That was exactly my goal, but even if I was able to get it out of that mode, I wasn't sure it would work because the backup was taken from the mirror not from the principal and would have had a different Checkpoint which the mirror would then see and deny.
Below are you suggestions with my comments:
Stop connections to the primary. - This I can't do because primary needs to be running 24/7.
If mirroring not already in synchronous mode switch to that mode. - I did this
Make sure databases are synchronised. - This was true.
Break mirroring (alter database dbname set partner off) - Did this.
bring target online and back it up - How do I do this? FORCED failover didn't work
Copy that backup to target2 and restore with norecovery - Didn't do any of this because I was unable to take it out of Restoring mode.
take a log backup of primary
Copy to target2 and restore with norecovery
set up mirroring to target2
For what it's worth, I also tried just breaking the mirror, shutting off SQL Server on Target1, moving the data and log files over to Target2 and starting SQL Server on 2 (complete hack I know), but it just brought the database up in a normal online mode like mirroring wasn't just interrupted.
June 1, 2010 at 3:16 pm
you are right the restore won't work as the tran log recovery is not from the database that had the full backup.
That was your last hope, you will have to initiate mirroring to target2 from the primary with a backup\copy\restore.
I don't see why you could not bring the mirror database online. After set partner off, on the mirror did you run
restore database dbname with recovery to make it usable?
Actually that would also put the mirror database on a different recovery path to the primary so my initial method would never have worked, apologies for that, should have investigated more, but thats why i said test it first.
You have no choice but to break mirroring to target1 and create mirroring to target2 instead.
---------------------------------------------------------------------
June 3, 2010 at 10:43 pm
Actually, what you suggest, using a restore db with recovery does work. I normally use lightspeed for restores and didn't know how to just do a plain recovery but I tried it and it worked perfectly.
Too bad it's a no go on the rest. Would have made my job easier if there was an easy way to move them around, but at least I wasn't missing something stupid.
Thanks for your help George.
-Jim
June 4, 2010 at 7:26 am
no probs, thanks for posting back
---------------------------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply