Database Mirroring Failover- all databases in a instance

Posted by
--Script to failover all databases in a instance. 
declare  @mirroring  table (query varchar(200)) 
insert into  @mirroring 
select 'use master;' 
insert into  @mirroring 
SELECT ' ALTER DATABASE '+quotename(db_name(database_id))+' SET PARTNER FAILOVER ;'   
FROM sys.database_mirroring WHERE 
mirroring_role_desc = 'PRINCIPAL' 
select * from @mirroring

-- Script to Remove Database Mirroring for all databases after failover (useful in cut-over)

declare  @mirroring  table (query varchar(200))  
insert into  @mirroring  select 'use master;'  
insert into  @mirroring  
SELECT ' ALTER DATABASE '+quotename(db_name(database_id))+' SET PARTNER OFF ;'    
FROM sys.database_mirroring 
WHERE  mirroring_role_desc = 'PRINCIPAL'  
select * from @mirroring

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s