Tuesday, 10 December 2013

Failing over all database mirrors to Secondy server

You box is dying fast!  You need to get your databases to DR.  No one wants to sit and right click each.  So here is the quick way:-

DECLARE @DBName sysname
Declare @SQL nvarchar(1000)
Set NoCount On;
/*Get first database where it is online and part of a mirror as principle role and is synced*/
SET @DBName = (select min (name)
     from sys.databases
     where [state] = 0
     and database_id in (select distinct(m.database_id)
          from sys.database_mirroring m
          where m.mirroring_role = 1
           And mirroring_state = 4)
     )
while @DBName is not null
BEGIN
 Print @DBName

  /*Alter database to fail over to partner*/
   Set @SQL = 'Alter Database ' + quotename(@DBName) + ' Set Partner Failover;'
  Print @SQL
   Exec sp_executesql @SQL;
     
   /*Get next database that is online and part of a mirror as partner role that is synced*/
   set @DBName = (select min( name)
     from sys.databases
     where [state] = 0
     and database_id in (select distinct(m.database_id)
          from sys.database_mirroring m
          where m.mirroring_role = 1
           And mirroring_state = 4)
     and name > @DBName
     )
END