Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Database Mirroring FAQ: Why is my mirror database in a restoring state?

Question: Why is my mirror database in a restoring state?

This question was asked on a technical discussion group. This is a follow up to an earlier discussion regarding troubleshooting database mirroring setup. He was able to get database mirroring set up with the help of one of my colleagues, Balmukund Lakhani. My reply follows.

Hi,

I am still working on DB mirroring with principal and mirror. There will be no Witness. This is what I did and my current issue:

  1. I have two SQL servers with a database called SCMAX on them. This is as simple as File ? New database. There is no application involved here.
  2. I did set up mirroring with Balmukund’s help. He was great. There is no witness. It is asynchronous mode.
Issue
Now all I want to do is – I created a table called Contacts in the principal and hoped it would show up in the mirror as well. I created some data in it too. But mirrored Database doesn’t do anything. I can’t even view the tables in it. It is as if it froze. All it says is Restoring. So I did paused the mirror. That didn’t help. I deleted the mirroring session. Even then the Mirrored database says restoring. How can I get rid of that? I want to view if my contacts moved over to mirrored database.

My answer:

The mirroring database will always be in a restoring state until it is brought online. That is the natural state of it. To see what is in it, you can create a database snapshot of the mirror database if you are using Enterprise Edition or Developer Edition (if not in production). Remember that a snapshot is a point in time, so you will only see things that already exist. If you add new things, you’ll need to create a new database snapshot.

To bring the mirror online manually after dropping mirroring, issue the following command (you can’t do it while it is still mirrored):

Restore Database <Database Name> With Recovery;
Or just manually failover the database to the mirror.

Comments

Posted by Jason Shadonix on 12 March 2009

I don't think that answer is quite correct.

When a mirroring session is properly configured, it should say "synchronized", "synchronizing", or "disconnected".  If it says "restoring" the mirror session is not running properly.

"Restoring" should only be the status after you do the restore on the mirror server before you set up the mirroring session (because you do a restore with NORECOVERY), or after you turn off the mirroring session (ALTER DATABASE <dbname> SET PARTNER OFF) before you recover it (RESTORE DATABASE <dbname> WITH RECOVERY).

Posted by mlemay on 12 March 2009

I'm not sure about SQL 2005 but on SQL 2008 I have the "Operating Mode" set to High Safety without automatic failover (synchronous).  In Management Studio the database being mirrored shows (Principal, Synchronized) next to the database name.  The mirror destination database shows (Restoring...) as it would if it was receiving data from Log Shipping.  If I display Mirroring under the Properties tab of the principal database, in the box marked Status: it shows "Synchronized:  the databases are fully synchronized".  So to me everything is working as it should be.  Maybe the mirror destination database would show a different status if it's in a different "Operating Mode"

Posted by Robert Davis on 12 March 2009

I think you are confusing Mirroring State with Database State. Mirroring State is the state of mirroring and displays as Jason described above as "synchronized" or "disconencted" or one of the other mirroring states. This state can be found in the sys.database_mirroring system view. Database state is the state of the database as shown in the sys.databases view.

A properly configured, synchronized mirror database will have a mirroring state of "synchronized" and a database state of "restoring".

In Object Explorer of Management Studio, the mirror partner will display its status as restoring as well as the mirroring state and the mirroring role. It appears next to the database name in the format <DB Name> (<Mirroring role>, <Mirroring state> / <Database state>).

Run the following query on a server with a mirror partner on it to see what I am talking about:

Select D.name,

D.state_desc As DatabaseState,

DM.mirroring_state_desc as MirroringState,

DM.mirroring_role_desc As MirroringRole

From sys.databases D

Inner Join sys.database_mirroring DM On DM.database_id = D.database_id

Where DM.mirroring_guid is not null

Posted by Jason Shadonix on 12 March 2009

Ah, I stand corrected.  The database state is indeed restoring.  

I just made the assumption that he was looking at the database in the object explorer in SSMS (which as you said shows both the mirroring and database state) and only seeing restoring, in which case mirroring is not properly working.  If you are looking in other places my original comment may be off base.

Posted by Robert Davis on 13 March 2009

I think a lot of DBA's and operational engineers have been trained to think that seeing "restoring" next to a database's name is a bad thing and their internal alarms go off. This is the correct response just about any other time, so it's certainly understandable.

Posted by tectigre on 13 March 2009

all the turns what I try restore a backup of the sql 2000 about to 2005 generates this typo I eat I do to resolve this quiz

ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: Could not continue scan with NOLOCK due to data movement. (Microsoft.SqlServer.Express.Smo)

Posted by Robert Davis on 14 March 2009

That's not nearly enough information to try to diagnose your problem. Are you trying to restore the full backup or the transaction log when you get this error?

If the full backup, are you trying to restore over an existing database or create a new database?

Please post your repro steps, and will try to help.

Posted by Laura Grob on 17 February 2010

Question.... I am trying to use a combination of snapshots and mirroring for a DR scenario.  Our plan was to use mirroring to our DR site but, due to constraints on legacy applications, I need to be able to recover 24 hours back.  My thought was to take a snapshot of the mirror every 24 hours but, I  cannot seem to find a way to recover the snapshot then.  Is this possible?

Posted by Robert Davis on 17 February 2010

Hi Laura. I'm not sure I understand exactly what you want to do.

If you want to recover the database snapshot as a new stand-alone database, it can't be done.

If you want to recover the mirror database to any of the previous database snapshots that exist, you can do that, but you would first have to drop database mirroring, recover the mirror, and drop any extra database snapshots before you could revert the database to a database snapshot.

If you simply want to know if it is feasible to create a database snapshot once per day and maintain daily snapshots for querying by the application, then absolutely.

Posted by shiv-356842 on 23 February 2012

Hi Team,

I have one question here

in Mirror server my database was INRECOVERY Mode can you please suggest me on this

in principal server your command is working what about in Mirror server.

Leave a Comment

Please register or log in to leave a comment.