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.
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):
Or just manually failover the database to the mirror.Restore Database <Database Name> With Recovery;
