Here is the script you can use to bring the mirroring database out of its “Restoring…” coma:
alter database MyDb set partner off
restore database MyDb with recovery
This is useful when you have high performance mirroring setup, your principal blows up, and there is no witness present. At this point, you will have to use the script, because you will not be able to access the mirroring database. Notice that the automatic failover does not work in this case. Applications and users need to point to the new database to resume their work.
Thanks to the folks who asked me those questions.
While I am on the topic of mirroring, the mirrored database must have the same name as the that of the principal, at least from my experience so far.
2 responses to “Bring back the mirror when the principal blows up and there is no witness”
After the automatic failover, would the connection string “Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;”
still work, since the server name has changed? Do the logins need to be recreated on the mirrored server?
Also, if two databases involved in one application and they are both mirrored, one database failed, so the result woudl be one database on one server, the other on the mirrored server?
Yes, the connection string would still work. However, it is your responsibility to make sure the logins are correct with the right SID and default database.
The answer is Yes to the second question.