HeavenCore | IT Solutions Built on Open Source Software
View Thread
 Print Thread
SQL Mirroring - Fore Mirror Online (when principle dies before failing over)
admin
Say you have a mirrored database and the principle goes offline due to a sudden hardware failure, you can bring the mirror online without the need for a witness by running the following:


ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS


Note that the ALLOW_DATA_LOSS flag has to be used, this basically means that any incomplete transactions from the principle that have not been committed yet are going to get rolled back and discarded - usually an acceptable compromise in the event of primary server failure.

If you have several mirrored databases you need to bring online, you can wrap it up in a quick cursor:


SET NOCOUNT OFF   

DECLARE @strSQL NVARCHAR(200)
DECLARE @strDatabasename NVARCHAR(50)
DECLARE @Cursor_CurrentMirrors CURSOR

SET @Cursor_CurrentMirrors = CURSOR FAST_FORWARD
FOR
   SELECT  name
   FROM    master.sys.databases a
         INNER JOIN master.sys.database_mirroring b ON a.database_id = b.database_id
   WHERE   NOT mirroring_guid IS NULL
         AND mirroring_role_desc = 'MIRROR' 
OPEN @Cursor_CurrentMirrors   
FETCH NEXT FROM @Cursor_CurrentMirrors INTO @strDatabasename   
WHILE @@Fetch_Status = 0
    BEGIN   
        SET @strSQL = 'ALTER DATABASE ' + @strDatabaseName + ' SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS'   
        EXEC sp_executesql @strSQL   
        PRINT 'Running: ' + @strSQL
        FETCH NEXT FROM @Cursor_CurrentMirrors INTO @strDatabasename   
    END   
CLOSE @Cursor_CurrentMirrors   
DEALLOCATE @Cursor_CurrentMirrors   

Jordon Pilling | Heavencore Administrator
 
Jump to Forum