locked
Cannot recreate the mirroring RRS feed

  • Question

  • Hello,

    I have a set of three servers for a SQL mirroring.

    SOPGENSQL1 (Principal)

    SOPGENSQL2 (Mirror)

    VOPGENSQLWIT (Witness)

    SOPGENSQL1 & SOPGENSQL2 were physical HP servers. We have done a P2V conversions on SOPGENSQL2.

    But now the mirroring does not work anymore...

    When trying to redo the mirroring I am getting a error:

    Click “Start Mirroring”

    What went wrong?

    on SOPGENSQL1

    SELECT

    state_desc FROMsys.database_mirroring_endpoints1

    result = STARTED

    on SOPGENSQL1

    SELECTroleFROMsys.database_mirroring_endpoints;

    GO

    result = 1

    --------------------------------------------------------------------------------

    on SOPGENSQL2

    SELECT

    state_desc FROMsys.database_mirroring_endpoints1

    result = STARTED

    on SOPGENSQL2

    SELECTroleFROMsys.database_mirroring_endpoints;

    GO

    result = 3

    Thanks,

    Dom


    Security / System Center Configuration Manager Current Branch / SQL


    • Edited by Felyjos Thursday, March 14, 2019 10:38 PM
    Thursday, March 14, 2019 10:16 PM

Answers

  • Hello,

    --===================================================================

    ALTER DATABASE MOODLE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO

    --===================================================================

    RESTORE DATABASE MOODLE
        FROM DISK = 'C:\Temp\MOODLE.bak'
        WITH REPLACE,NORECOVERY
    GO

    RESTORE LOG MOODLE
        FROM DISK = 'C:\Temp\MOODLE-LOG.bak'
        WITH FILE=5, NORECOVERY
    GO
    RESTORE LOG MOODLE FROM DISK = 'C:\Temp\moodle_backup_2019_03_15_080002_3305139.TRN'
    WITH NORECOVERY
    GO
    --===================================================================
    ALTER DATABASE Moodle
    SET MULTI_USER;
    GO

    --===================================================================

    I had to add the latest backup of the log to complete the restoration

    The mirroring started successfully now

    Thanks

    Dom


    Security / System Center Configuration Manager Current Branch / SQL

    • Marked as answer by Felyjos Friday, March 15, 2019 5:14 PM
    Friday, March 15, 2019 5:14 PM

All replies

  • and what is the status of the database on the Mirror server? When you initialized the database on the mirror using full backup and the transaction log backup of principal, did you restore with NO RECOVERY? or accidentally recovered it? 

    Remember, the database (on the mirror) has to be in restoring mode (and not recovered) for mirroring to be successfully configured. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.


    Thursday, March 14, 2019 10:59 PM
  • run query on mirror server..

    select database_id, user_access_desc, state_desc, recovery_model_desc, is_broker_enabled, log_reuse_wait_desc from sys.databases where name = 'Moodle'

    If the mirror database is in the ONLINE state, you will need to restore the database and the subsequent T-Log backups again with NORECOVERY option before setting the mirroring partners.


    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    Friday, March 15, 2019 2:39 AM
  • and what is the status of the database on the Mirror server? When you initialized the database on the mirror using full backup and the transaction log backup of principal, did you restore with NO RECOVERY? or accidentally recovered it? 

    Remember, the database (on the mirror) has to be in restoring mode (and not recovered) for mirroring to be successfully configured. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.


    Hello,

    Apparently the databases after SOPGENSQL2 was converted to VM went into recovery mode ... then restoring. I think there was one step missing before converting... may be removing the mirroring or something else... the mirror was still active when the task of conversion was started...

    Thanks,

    Dom


    Security / System Center Configuration Manager Current Branch / SQL


    • Edited by Felyjos Friday, March 15, 2019 2:45 AM
    Friday, March 15, 2019 2:44 AM
  • run query on mirror server..

    select database_id, user_access_desc, state_desc, recovery_model_desc, is_broker_enabled, log_reuse_wait_desc from sys.databases where name = 'Moodle'

    If the mirror database is in the ONLINE state, you will need to restore the database and the subsequent T-Log backups again with NORECOVERY option before setting the mirroring partners.


    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    Hello,

    Yes the moodle database is in the ONLINE state.

    I will start a backup on the Principal :

    USE master;
    GO
    ALTER DATABASE SRRS 
    SET RECOVERY FULL;
    GO
    BACKUP DATABASE SRRS 
        TO DISK = 'C:\Temp\SRRS.bak' 
        WITH FORMAT
    GO
    BACKUP LOG SRRS 
        TO DISK = 'C:\Temp\SRRS-LOG.bak' 
    GO
    
    and then a new restoration on the mirrored...
    RESTORE DATABASE MOODLE 
        FROM DISK = 'C:\Temp\MOODLE.bak' 
        WITH REPLACE,NORECOVERY
    GO
    RESTORE LOG MOODLE 
        FROM DISK = 'C:\Temp\MOODLE-LOG.bak' 
        WITH FILE=1, NORECOVERY
    GO
    RESTORE LOG MOODLE 
        FROM DISK = 'C:\Temp\MOODLE-LOG.bak' 
        WITH FILE=2, NORECOVERY
    GO
    RESTORE LOG MOODLE 
        FROM DISK = 'C:\Temp\MOODLE-LOG.bak' 
        WITH FILE=3, NORECOVERY
    GO
    RESTORE LOG MOODLE 
        FROM DISK = 'C:\Temp\MOODLE.bak' 
        WITH FILE=4, NORECOVERY
    GO

    Thanks,

    Dom


    Security / System Center Configuration Manager Current Branch / SQL




    • Edited by Felyjos Friday, March 15, 2019 3:03 AM
    Friday, March 15, 2019 2:47 AM
  • OK, that explains why that error showed up. You will now have to start over and restore a full and T-log backup of the principal DB with No Recovery on to Mirror and re-try configuring Mirroring. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Friday, March 15, 2019 2:51 AM
  • the database seems to be restored but the log is giving errors..

    Processed 451288 pages for database 'MOODLE', file 'moodle' on file 1.
    Processed 5 pages for database 'MOODLE', file 'moodle_log' on file 1.
    RESTORE DATABASE successfully processed 451293 pages in 156.945 seconds (22.464 MB/sec).

    Msg 4326, Level 16, State 1, Line 1 The log in this backup set terminates at LSN 420000011765400001, which is too early to apply to the database. A more recent log backup that includes LSN 3774000007682300001 can be restored. Msg 3013, Level 16, State 1, Line 1 RESTORE LOG is terminating abnormally.

    How many log should I restore?

    and the database still restoring after 2 hours !!!

    Thanks,

    Dom


    Security / System Center Configuration Manager Current Branch / SQL



    • Edited by Felyjos Friday, March 15, 2019 5:38 AM
    Friday, March 15, 2019 4:14 AM
  • Hi Dom,
    According to your error information, it could be a LSN mismatch issue. You need follow the log backup chain to restore from log backup. You can use below T-SQL to query backup LSN.

    SELECT DISTINCT s.first_lsn, s.last_lsn,  
        s.database_backup_lsn, 
                    s.checkpoint_lsn,
                    s.backup_finish_date, 
        s.type, y.physical_device_name
    FROM msdb..backupset AS s INNER JOIN 
    msdb..backupfile AS f ON f.backup_set_id = s.backup_set_id INNER JOIN 
    msdb..backupmediaset AS m ON s.media_set_id = m.media_set_id INNER JOIN 
    msdb..backupmediafamily AS y ON m.media_set_id = y.media_set_id 
    WHERE  (s.database_name = 'LSNtest')  
    ORDER BY  s.backup_finish_date DESC;
    Hope it is helpful for you.
    Best regards,
    Cathy Ji


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, March 15, 2019 9:50 AM
  • Hello,

    this is the result of the query...

    As I am using the backup 3/14/2019 7:54 PM there is only one log... am I right?

    Also I checked the database is still listed as "Restoring" after 7 hours+!!! something should be wrong, how to stop it and restart?

    The result of a new backup is:

    -----------------------------------------------------------------------------------------------

    Processed 451648 pages for database 'MOODLE', file 'moodle' on file 1.

    Processed 5 pages for database 'MOODLE', file 'moodle_log' on file 1.

    BACKUP DATABASE successfully processed 451653 pages in 11.383 seconds (309.983 MB/sec).

    Processed 525 pages for database 'MOODLE', file 'moodle_log' on file 5.

    BACKUP LOG successfully processed 525 pages in 0.202 seconds (20.280 MB/sec).

    ----------------------------------------------------------------------------------------------------------------

    then

    ----------------------------------------------------------------------------------------------------------------


    ... on SOPGENSQL1..

    So I am starting a new restore on SOPGENSQL2

    RESTORE DATABASE MOODLE 
        FROM DISK = 'C:\Temp\MOODLE.bak' 
        WITH REPLACE,NORECOVERY
    GO

    Result

    Processed 451648 pages for database 'MOODLE', file 'moodle' on file 1.
    Processed 5 pages for database 'MOODLE', file 'moodle_log' on file 1.
    RESTORE DATABASE successfully processed 451653 pages in 144.202 seconds (24.469 MB/sec).

    Still in Restoring mode..

    So starting the restoration of the log:

    RESTORE LOG MOODLE 
        FROM DISK = 'C:\Temp\MOODLE-LOG.bak' 
        WITH FILE=5, NORECOVERY
    GO


    Processed 0 pages for database 'MOODLE', file 'moodle' on file 5.
    Processed 525 pages for database 'MOODLE', file 'moodle_log' on file 5.
    RESTORE LOG successfully processed 525 pages in 0.294 seconds (13.934 MB/sec).

    but still in restoring mode... !!! (and mirroring is not set!)

    should it stays in Restoring (because of NO RECOVERY) ?

    Trying to start the mirroring from SOPGENSQL1 and failed

    Thanks,

    Dom


    Security / System Center Configuration Manager Current Branch / SQL






    • Edited by Felyjos Friday, March 15, 2019 3:41 PM
    Friday, March 15, 2019 1:25 PM
  • Hello,

    So far the only way to prevent the "Restoring" mode to come up and stay is

    RESTORE DATABASE Moodle WITH RECOVERY

    GO

    Is it valid as it fails on mirroring?

    or should  try again a new

    RESTORE DATABASE MOODLE   

    FROM DISK='C:\Temp\MOODLE.bak' WITH REPLACE, NORECOVERY

    GO

    RESTORE LOGMOODLE  

    FROM DISK='C:\Temp\MOODLE-LOG.bak' WITHFILE=5, NORECOVERY

    GO

    Mirroring fails also with a different error message:

    What to do next for the mirroring?

    Thanks,
    Dom


    Security / System Center Configuration Manager Current Branch / SQL


    • Edited by Felyjos Friday, March 15, 2019 4:01 PM
    Friday, March 15, 2019 3:51 PM
  • Hello,

    --===================================================================

    ALTER DATABASE MOODLE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO

    --===================================================================

    RESTORE DATABASE MOODLE
        FROM DISK = 'C:\Temp\MOODLE.bak'
        WITH REPLACE,NORECOVERY
    GO

    RESTORE LOG MOODLE
        FROM DISK = 'C:\Temp\MOODLE-LOG.bak'
        WITH FILE=5, NORECOVERY
    GO
    RESTORE LOG MOODLE FROM DISK = 'C:\Temp\moodle_backup_2019_03_15_080002_3305139.TRN'
    WITH NORECOVERY
    GO
    --===================================================================
    ALTER DATABASE Moodle
    SET MULTI_USER;
    GO

    --===================================================================

    I had to add the latest backup of the log to complete the restoration

    The mirroring started successfully now

    Thanks

    Dom


    Security / System Center Configuration Manager Current Branch / SQL

    • Marked as answer by Felyjos Friday, March 15, 2019 5:14 PM
    Friday, March 15, 2019 5:14 PM
  • Hello,

    One question as I would like to do the conversion from Physical to Virtual of SOPGENSQL1 which is the Principal in the current mirror what would be the steps to prevent all these issues seen previously.

    1. Failover from 1 to 2

    2. Remove the mirror.

    3. do the conversion

    4. do the mirror

    ???

    Thanks,

    Dom


    Security / System Center Configuration Manager Current Branch / SQL

    Friday, March 15, 2019 6:04 PM