How to Manually Failover a SQL Server Database Mirroring Session

Transact-SQL:
To manually failover (swap principal and mirror server roles) SQL Server database mirroring session when the database is in the SYNCHRONIZED state using T-SQL, run this code from the principal server:

database_synchronized_failover_mirroring

USE master;
ALTER DATABASE [Database_Name] SET PARTNER FAILOVER

The mirror database becomes the principal and the principal database becomes the mirror, clients are disconnected from the former principal database and active transactions are rolled back:

"Nonqualified transactions are being rolled back. Estimated rollback completion: 100%."

database_complete_failover_mirroring

If you try to manually failover SQL Server database mirroring session when the database is NOT in the SYNCHRONIZED state, you will get an error message:

Msg 1422, Level 16, State 2, Line 2
The mirror server instance is not caught up to the recent changes to database "Database_Name". Unable to fail over.

database_error_failover_mirroring

SQL Server Management Studio:
To manually failover SQL Server database mirroring session when the database is in the SYNCHRONIZED state from SQL Server Management Studio:

Connect to the principal server instance -> Right-click the database -> select Tasks -> click Mirror:

database_tasks_mirror

Click 'Failover':

database_synchronized_failover_mirroring_ssms

Click ‘Yes’ to confirm:

"Failing over database mirroring will swap the roles of the mirror and principal databases. The mirror database will become the principal database, and the current principal database will become inaccessible. If you have just modified any properties in the Database Properties dialog box, those changes will be lost. In addition, SQL Server must close all other connections to the current principal database."

database_synchronized_failover_mirroring_ssms_confirm

Database Properties dialog box will close automatically if the failover succeeds.

If the post helped you, please share it:
Pin It

2 comments to How to Manually Failover a SQL Server Database Mirroring Session

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">