I can execute a fail-over if both the primary and the mirror partners are available, but I can't figure out how to do a manual fail-over when the principal is switched off or unplugged from the network (i.e. fails).
I tried using:
ALTER DATABASE myMirrorDatabase SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
But this puts the db into the following state:
[In Recovery]
I can't run RESTORE DATABASE, because the db is configured for mirroring. So what is the proper way of doing this? Help!
Further info... (given SERVER_A with DB_A and SERVER_B with DB_B)
Initial State:
DB_A is [Principal, Synchronised], DB_B is [Mirror, Synchronised / Restoring]
I stop SERVER_A:
DB_A is [offline], DB_B is [Mirror, Disconnected / In Recovery]
I run the alter database statement above:
DB_A is [offline], DB_B is [In Recovery]
If you have an unplanned failover where the principal and witness aren't available you should issue the following statements on the mirror:
USE master
go
ALTER DATABASE AdventureWorks SET PARTNER OFF
GO
RESTORE DATABASE AdventureWorks WITH RECOVERY
GO
This will bring the mirror online.
If you need to re-establish the Mirror configuration once the principal is available again you need to go through the process of backing up the mirror DB (because it's now the most recent version), restoring to the principal and then configuring mirroring all over again but in reverse. At the end of it the mirror will become the new principal.