I have 3 mirrored databases on a single instance of sqlserver2008-R2 in a high safety with automatic failover config. Is there a way to trigger automatic failover of just 1 database?

I want to simulate where the database actually is unreachable and not just hit the 'Failover' button from SSMS. I can stop the endpoint on the primary but that would make all 3 databases unreachable as they use the same endpoint.

How do I disable just 1 db?

3 Answers 3


If your mirroring is configured correctly for high safety/automatic failover, then I would be inclined to think that you could just take the principal database offline with this:

alter database YourDb set offline
with rollback immediate

I believe by taking the database offline, you will have automatic failover without affecting the other two databases that you don't want to failover.

  • 1
    this is the solution i had hoped would work but I get a msg 1468, the database is involved in mirroring, alter statement failed.
    – dat_girl
    Oct 14, 2011 at 13:24
  • @dat_girl can you first try setting it as single user with rollback immediate and then try the above statement? Oct 14, 2011 at 13:26
  • That didn't work either @shark, same error. I think I would have to stop mirroring before any 'state' changes can be made to the db.
    – dat_girl
    Oct 14, 2011 at 14:38

For mirroring to make sense, you'll need at least a second instance of SQL-Server: One for the "principal" and one for the "mirror".

How to install a further instance, see: Instance configuration.

For automatic failover to take effect, you'll also need a third instance (the "Witness"), which purpose is to monitor the principal's and mirror's (connection)states ("... the witness does not serve the database. Supporting automatic failover is the only role of the witness").

To setup the whole mirror with failover see: Automatic failover and Synchronous Database Mirroring (High-Safety Mode).

Once completed the steps, you can test your configuration by stopping one of the SQL-Server services (principal or mirror). The mirrored database gets available then.

I strongly suggest not to run the instances on the same, real server since this will not secure you from any hardware-disasters!

  • Thanks @vorax, but I already have mirroring setup and it works fine. I also don't want to stop the service as that would bring down all the databases on the instance. I just want to stop 1.
    – dat_girl
    Oct 14, 2011 at 13:08

Hmm, alter database whateverdb set partner failover ought to do it, but it might take the entire server with it, I don't have one to hand that I can experiment with, and the docs annoyingly seem to use server and database interchangeably.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.