Pastebin launched a little side project called VERYVIRAL.com, check it out ;-) Want more features on Pastebin? Sign Up, it's FREE!
Guest

Fail over all SQL Server mirrored databases

By: a guest on Nov 15th, 2012  |  syntax: T-SQL  |  size: 0.66 KB  |  views: 246  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. DECLARE @DBName AS NVARCHAR(128)
  2. DECLARE @SQL AS NVARCHAR(200) ;
  3.  
  4. DECLARE DBCursor CURSOR FAST_FORWARD FOR
  5.     SELECT
  6.            D.name
  7.     FROM
  8.            sys.database_mirroring M
  9.     JOIN
  10.            sys.databases D
  11.     ON
  12.            M.database_id = D.database_id
  13.     WHERE
  14.            mirroring_role_desc = 'PRINCIPAL'
  15.  
  16.  
  17. OPEN DBCursor
  18. FETCH NEXT FROM DBCursor
  19. INTO @DBName ;
  20.  
  21. WHILE @@FETCH_STATUS = 0
  22. BEGIN
  23.     PRINT 'Failing over ' + @DBName + '...' ;
  24.  
  25.     SELECT @SQL = 'ALTER DATABASE ' + @DBName + ' SET PARTNER FAILOVER' ;
  26.    
  27.     EXEC(@SQL) ;
  28.    
  29.     FETCH NEXT FROM DBCursor
  30.     INTO @DBName
  31. END
  32.  
  33. CLOSE DBCursor ;
  34. DEALLOCATE DBCursor ;
clone this paste RAW Paste Data