April 10, 2012 at 2:31 pm
Hello Guys,
I am performing mirroring on two different instances of sql server 2008 (Evaluation version for testing) on same server.
Step1. I created database "MirrorTest" on principal server, whose recovery model is set to 'Full'.
Step2. I took up full backup of database Mirrortest of prinicipal server.
Step3. I restored full backup on mirrored server.
Now the database which I restored backup is still showing status "Restoring", its more than an hour but its status is not changing ! I refreshed it but no luck. I deleted that db and have tried again but same issue. I attached the screen shot for reference.
Please help me.
Thanks in advance.
April 10, 2012 at 2:35 pm
The mirror database needs to be in restoring mode for you to configure database mirroring. That is why the instructions say to restore the backup using WITH NORECOVERY.
April 10, 2012 at 2:39 pm
Yes Pettis,
I selected the option " Restore With Norecovery" during restore of db. But still the same status.
April 10, 2012 at 3:10 pm
jitendra.padhiyar (4/10/2012)
Yes Pettis,I selected the option " Restore With Norecovery" during restore of db. But still the same status.
That is right, it isn't going to change. Now you need to go through the steps to setup mirroring.
April 10, 2012 at 4:14 pm
jitendra.padhiyar (4/10/2012)
Yes Pettis,I selected the option " Restore With Norecovery" during restore of db. But still the same status.
you also need to take and restore at least 1 transaction log backup, the restore will use the WITH NORECOVERY clause
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 10, 2012 at 4:57 pm
Perry Whittle (4/10/2012)
jitendra.padhiyar (4/10/2012)
Yes Pettis,I selected the option " Restore With Norecovery" during restore of db. But still the same status.
you also need to take and restore at least 1 transaction log backup, the restore will use the WITH NORECOVERY clause
You know, I had to do that at a previous employer to get mirroring to work, but at another previous employer (after the one I set up mirroring at) a DBA said that wasn't necessary. I was unable to test it there and I don't have the resources available right now to test it where I am right now either.
April 10, 2012 at 5:01 pm
Lynn Pettis (4/10/2012)
Perry Whittle (4/10/2012)
jitendra.padhiyar (4/10/2012)
Yes Pettis,I selected the option " Restore With Norecovery" during restore of db. But still the same status.
you also need to take and restore at least 1 transaction log backup, the restore will use the WITH NORECOVERY clause
You know, I had to do that at a previous employer to get mirroring to work, but at another previous employer (after the one I set up mirroring at) a DBA said that wasn't necessary. I was unable to test it there and I don't have the resources available right now to test it where I am right now either.
Its the same for setting up Always On too
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 11, 2012 at 9:48 am
I did that... I restored backup with NORECOVERY option and than restore transaction log backup with NoRecovery option.
I setup mirroring and can see "Principal, Synchronized" status of Primary database. Status of Mirror database (Secondary DB) was "In Reconvery" I wait for some time and than refresh it.. its now again "Restoring..." status. It should be "Mirror, Synchronised, Restoring..."
When it will be changed ?
April 11, 2012 at 10:43 am
jitendra.padhiyar (4/11/2012)
I did that... I restored backup with NORECOVERY option and than restore transaction log backup with NoRecovery option.I setup mirroring and can see "Principal, Synchronized" status of Primary database. Status of Mirror database (Secondary DB) was "In Reconvery" I wait for some time and than refresh it.. its now again "Restoring..." status. It should be "Mirror, Synchronised, Restoring..."
When it will be changed ?
Have you tried right clicking the databases folder in SSMS and refreshing?
Could you execute the following query against the principal and the mirror and post the results
select mirroring_state_desc, mirroring_role_desc,
mirroring_safety_level_desc, mirroring_partner_name,
mirroring_partner_instance, mirroring_witness_state
from sys.database_mirroring
where mirroring_state_desc is not null
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 11, 2012 at 11:09 am
Thanks for the response Perry !
Yes, I tried to refresh database but status "Restoring..." not chagned. I ran the query you given on both principal and mirror database and can say yes the mirroring is configured properly and it is working. Below is the result:
Result of Principal server Instance:
Mirroring_state_descMirroring_role_descMirroring_safety_level_descMirroring_partner_nameMirroring_partner_instanceMirroring_witness_state
SynchronizedPrincipalFULLTCP://JPTEST:5023JPTEST\Mirrorinst1
SynchronizedPrincipalFULLTCP://JPTEST:5023JPTEST\Mirrorinst1
============================================================
Result of Mirror Server Instance:
Mirroring_state_descMirroring_role_descMirroring_safety_level_descMirroring_partner_nameMirroring_partner_instanceMirroring_witness_state
SynchronizedMirrorFULLTCP://JPTEST:5022JPTEST\SQLINST1
SynchronizedMirrorFULLTCP://JPTEST:5022JPTEST\SQLINST1
April 11, 2012 at 1:02 pm
It is working... !! I just try to failover it and could see the chagnes in status of mirror database from "Restoring..." to "Principal, Synchronized" and vice-versa.
Thanks all of you for you guidence and help!!
April 11, 2012 at 1:16 pm
It is working... !! I just try to failover it and could see the chagnes in status of mirror database from "Restoring..." to "Principal, Synchronized" and vice-versa.
Thanks all of you for you guidence and help!!
April 11, 2012 at 1:19 pm
Glad you're sorted
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply