Tell me more ×
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.
up vote 2 down vote favorite

i'm doing some tests to build one database with one mirror database and one witness.

so far, i'm failing in the first test, I want to kill the witness, then the Main database, and then put the Mirror database working as Main database.

So when I killed the witness,and then the Main database and did

ALTER DATABASE [MyDataBase] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

but i got:

Msg 1455, Level 16, State 106, Line 1 The database mirroring service cannot be forced for database "MyDataBase" because the database is not in the correct state to become the principal database.

i've been watching this tutorial http://www.youtube.com/watch?v=f67gfslkQew and in that video everything goes just has planed.

you can see here my databases and my query http://i42.tinypic.com/vp8chh.png to see if i'm missing something.

I'm having the same problem with both principal and mirror up, i'm unable to force mirror to turn to principal.

what am I missing?

edit: I can put the mirror database up with these commands

ALTER DATABASE [MyDatabase] SET PARTNER OFF
RESTORE DATABASE [MyDatabase] WITH RECOVERY

but if I do this I loose my mirroring settings, and when the other database comes up, is not connected to this one.

share|improve this question
If both the mirror and the primary are up but the witness is gone then you can force a failover to the mirror by running "ALTER DATABASE mydb SET PARTNER FAILOVER" on the primary and the database on the mirror should become available and this will not trash the mirroring settings. – Jason Cumberland Mar 12 '12 at 16:21

migrated from stackoverflow.com Nov 8 '11 at 23:57

2 Answers

up vote 1 down vote

Backup your primary database and restore that to your mirror instance, then backup your primary log and restore that to the mirror instance, both with norecovery. After that you need to configure mirroring (with or without a witness) netween the primary and the mirror. After the mirroring has been configured you can force a failover by running "alter database mydb set partner failover" on the primary. Once the database fails over the old primary should show the database as "restoring" and the old mirror should show the database as accessible. To fail the database back to the old primary run the same statement on the mirror.

You can not "blow away" the witness if you want to have automatic failvoer, but if you don't want automatic failover then you don't need a witness anyway.

share|improve this answer
up vote 1 down vote

Your secondary database needs to be in a "Restoring..." state. Can you verify that? When you restore the database on the secondary server, it needs to be with NORECOVERY to put it in this state.

share|improve this answer
do I need to do something to put it in that state? or am I just not waiting enough ? (my secondary database is "In recovery" eternally..) – RagnaRock Nov 7 '11 at 18:30
@RagnaRock in the object explorer, is there anything around the database on the secondary server? Does it say "Restoring..." per chance? – Thomas Stringer Nov 7 '11 at 22:57
if the primary server is conected it says "Restoring..." (and I cant do a force_service) if I stop the primary server says "In Recovery" and still cant force_service – RagnaRock Nov 8 '11 at 10:13

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.