Failover Mirrored Databases Using SQL

SQL Stuff

By mikesdatawork on 2021-06-26 • ( Leave a comment )

Failover Mirrored Databases using only SQL. This isn’t terribly difficult to do but doesn’t hurt to have an automated solution for quick testing.

use [master];
set nocount on

-- check mirror state (principal or mirror)
select
db_name([database_id])
, [mirroring_role_desc]
, [mirroring_state_desc]
, [mirroring_partner_instance]
from
sys.database_mirroring
order by
db_name([database_id]) asc

-- failover only principal databases (this is all that is required to perform standard failover process in mirroring

declare @set_partner_failover varchar(max)
set @set_partner_failover = ''
select @set_partner_failover = @set_partner_failover +
'alter database [' + db_name([database_id]) + '] set partner failover;' + char(10)
from sys.database_mirroring where [mirroring_role_desc] = 'principal'
order by db_name([database_id]) asc

select (@set_partner_failover) for xml path(''), type

-- confirm mirror state (principal or mirror)
select
db_name([database_id])
, [mirroring_role_desc]
, [mirroring_state_desc]
, [mirroring_partner_instance]
from
sys.database_mirroring
order by
db_name([database_id]) asc

-- remove database mirroring (if required)
declare @set_partner_off varchar(max)
set @set_partner_off = ''
select @set_partner_off = @set_partner_off +
'alter database [' + db_name([database_id]) + '] set partner off;' + char(10)
from sys.database_mirroring where [mirroring_role_desc] = 'principal'
order by db_name([database_id]) asc

select (@set_partner_off) for xml path(''), type




		
	
	

		

Categories: SQL Stuff