Failover all databases to other server

Quick snippet I threw together to help with failing over synchronized databases to the other server in bulk. No way I want to click that darn Fail-over button repeatedly. This scripts the statements to print (i commented out the exec portion) so that you can preview the results and run manually.Note that it’s also useful to have a way to do this as leaving databases running on the mirror server for an indefinite period can violate licensing terms on the secondary server when it’s a fail-over server and not meant to be the primary.

Manually fail over all databases that are synchronized to the other server

GitHub

/*******************************************************
    List out databases to run through be processed automatically
    -- they need to be the principal and in synchronized state to 
    
    
    2016-02-04 16:07 sqlbarbarian
*******************************************************/
if object_id('tempdb..#dbList') is not null
    drop table #dbList;

select
    d.name
into #dbList
from
    sys.databases as d

    inner join sys.[database_mirroring] as DM
        on d.database_id = DM.database_id
        and DM.mirroring_role = 1  -- it is the principal
        and DM.mirroring_state = 4 -- it is in synchronized state


/*******************************************************
loop through all databases and execute query
*******************************************************/

declare @DBName sysname

declare curDatabase cursor fast_forward read_only local for
        select
            i.name
        from
            #dbList as i

open curDatabase

fetch next from curDatabase into @DBName

while @@fetch_status = 0
begin

    declare @Xsql nvarchar(max) = N'alter database ' + quotename(@DBName) + ' set partner failover; '
    --exec sys.sp_executesql @Xsql
    print @xsql
    fetch next from curDatabase into @DBName
end
close curDatabase
deallocate curDatabase