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: 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. I did set up mirroring with Balmukund’s help. He was great. There is no witness. It is asynchronous mode.IssueNow 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.
Restore Database <Database Name> With Recovery;
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).
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"
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
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.
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.
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)
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.
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?
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.
The following scripts can be downloaded as text files. You will need to change the file extension to .ps1 in order to execute them. Backup a database Restore a database Scan a server to find a free port Query DNS to get the FQDN of a server
To see some examples of my other forms of writing, please visit my page on WritersCafe.org. It is almost exclusively horror fiction, but I sometimes throw other things in there too from time to time. There's one science fiction story, a couple of poems, and quite a few humor pieces as well.
Look for me in the SQL Q&A section of the August, 2007 issue of TechNet Magazine. August issue of TechNet Magazine's SQL Q&A column
