View Thread
HeavenCore | IT Solutions Built on Open Source Software » HeavenCore Central » SQL Functions
 Print Thread
SQL Mirroring - Fore Mirror Online (when principle dies before failing over)
admin
Top  #1 Print Post
Posted on 19-10-2011 13:25
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:

Code Download source  

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:

Code Download source  

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
 
Web
Jump to Forum
8,382,805 unique visits
Powered by PHPFusion Copyright © 2024 PHP Fusion Inc
Released as free software without warranties under GNU Affero GPL v3.
Theme designed by Falcon
Copyright HeavenCore © 2005 - 2019 by Jordon Pilling
Data backups secured by Dekart Private Disk