SQL Stuff

Automatically Faliover All Mirrored Databases

The following SQL logic will automatically failover ALL your mirrored databases. It will first set all your databases to FULL SAFETY, then fail them over. One thing to keep in mind is that after you have run the code you should go to the secondary server and turn off full safety with ALTER DATABASE [MY_DATABASE] SET SAFETY OFF. This will bring them back to High Performance Mode.

 use master;
set nocount on

declare
@set_full_safety_on_mirror_databases	varchar(max) = ''
select
@set_full_safety_on_mirror_databases	= @set_full_safety_on_mirror_databases +
'alter database [' + cast(DB_NAME(database_id) as varchar(255)) + '] set safety full;' + char(10) from
sys.database_mirroring
where
mirroring_guid is not null
and mirroring_role_desc = 'PRINCIPAL'

--exec (@set_full_safety_on_mirror_databases)
select	(@set_full_safety_on_mirror_databases) for xml path(''), type

use master;
set nocount on

declare
@failover_mirror_databases	varchar(max) = ''
select
@failover_mirror_databases	= @failover_mirror_databases +
'alter database [' + cast(DB_NAME(database_id) as varchar(255)) + '] set partner failover;' + char(10) + char(10) from
sys.database_mirroring
where
mirroring_guid is not null
and mirroring_role_desc = 'PRINCIPAL'

--exec (@failover_mirror_databases)
select	(@failover_mirror_databases) for xml path(''), type
Advertisements

Categories: SQL Stuff

Leave a Reply

Fill in your details below or click an icon to log in:

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