Saturday, October 10, 2009

SQL Server 2005 Mirroring Failover

One of our customers uses SQL Server 2005 and mirrors a database between two locations.

Twice now our customer has performed a manual failover using the following commands (run at the secondary/mirror location):

  • ALTER DATABASE (NAME) SET PARTNER OFF (breaks the mirror but still says recovering...)

  • RESTORE DATABASE (NAME) WITH RECOVERY (returns the DB to a usable state & removes the recovering... status)


This evening we discovered a magical button that up to now had gone overlooked, FAILOVER.  Using this button you can immediately swap the roles between the mirror and the principal databases.

SQL Server 2005 Failover Button