Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are doing a planned failover and switch back from the primary to DR with mirroring. We do not have a separate monitor instance.

Please help me with the following 1. Is it possible to switch back without the testing changes made in DR. OR fallback will have to include the changes tested from DR? 2. Does taking a backup prior to DR test impact log backup/copy/restore process

Many thanks for your helpful responses.

share|improve this question
    
Are you using Log Shipping or Mirroring? Your title says Log Shipping but you mention mirroring in the body of the question. –  KenWilson Feb 4 at 16:15
    
We are using both log shipping and mirroring. Mirroring between server 1 and 2, server 3 and server 4. Log shipping between server 1 and server 3. I need to test failover from server 1 to server 3 and test mirroring between server 3 and server 4. After testing is completed switch over back to server 1 as primary with mirroring to server 2. Does that help? –  TDPSQL_Newbie Feb 4 at 16:27
    
Do I need to stop mirroring between server 1 and server 2 before dr from server 1 to server 3? –  TDPSQL_Newbie Feb 4 at 17:22
    
Could you clarify which database are mirrored on each server and which is log shipped. –  KenWilson Feb 4 at 18:56
    
Hi Ken Thank you for your time. DB1 is mirrored from server 1 to server 2. DB1 is log shipped from server 1 to server 3. DB1 on server 3 is mirrored to DB1 on server 4. –  TDPSQL_Newbie Feb 4 at 19:09

2 Answers 2

I would start this test FIRST by taking a copy_only backup, see here for details, of DB1 on server 1.

BACKUP DATABASE [DB1] TO DISK = N'<backup location>' WITH COPY_ONLY

This will let you get back to ground zero at the end or if something get messed up. Then you can turn off mirroring using the command

ALTER DATABASE DB1 SET PARTNER OFF;

After mirroring is turned off I would backup the tail of the log, see here for details, using the command

BACKUP LOG DB1 TO DISK = N'<backup location>' WITH NORECOVERY;

and then restore that backup to DB1 with recovery. That will bring the DR site online. At that point you can then setup mirroring from server 3 to server 4.

*Note: Script this out as much as possible. You do not want to be trying to do this manually if this was a real DR situation.

Also, if you have log shipping on server 1 and it is mirroring to server 2 you should setup all the log shipping jobs on server 2 so if you have a failover you can quickly enable the jobs and start log shipping from server 2 to server 3.

At the end of your testing you can restore the copy_only backup to server 1 reset up mirroring to server 2 and log shipping to server 3.

Hope that helps.

share|improve this answer
    
Hi Ken:Thanks much. You have clarified my concerns. Please confirm if I understand this correctly. 1. copy only backup will help be come back to current state if needed. 2. I have to remove mirroring to do swith over (I was not clear abt this). 3. Restore the backup of tail with recovery back to db1 - is this if I do not want changes from dr testing implemented with a switch back? Or is it restore this backup to log shipping db with recovery. I tested this but getting a confirmation from an experienced SQL DBA is a big help :) –  TDPSQL_Newbie Feb 5 at 20:23
    
1. Correct, using "WITH COPY_ONLY" will not break the backup sequence. 2. You do not HAVE to remove mirroring but taking the tail-log backup will break mirroring so removing it will mitigate that issue. 3. Restoring the tail-log backup with recovery will bring the log shipping database online so you can setup mirroring. At that point if you do not want any changes made on server 3 to be on server 1 when you return to the current state then you would need to use the copy only backup to restore to current state. Otherwise you can take a backup of DB1 on server 3 and restore it to server 1. –  KenWilson Feb 6 at 5:14
    
Thank you Ken for your support. It was helpful to get confirmations on my understanding. –  TDPSQL_Newbie Feb 6 at 17:52

In either case (Log Shipping or Mirroring) when you bring the DR server online for testing any changes will be replicated back to the production server. If you don't want this to happen you'll want to break the mirror or the log shipping and bring the DR server online, and point some app servers/clients to it.

When you do this the users will be able to make changes to production and DR at the same time, so be careful as to which one they are using.

When the DR test is done, you'll need to take the most recent backup from production and restore it to DR again, rolling transaction logs forward then setup mirroring or log shipping again from scratch.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.