Advertisements

SQL-BOX.COM

–SQL Server Storage

Database Mirroring Failover- all databases in a instance

--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
Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: