locked
Database Mirroring... RRS feed

  • Question

  • Hello,

    I have restored the DB with a .bak file with NORECOVERY.

    I have restored the transaction log .trn file with  NORECOVERY

    on the principal and mirrored servers but for more than an hour the two DBs are still showing "Restoring" and I could not set the mirror... (when selecting "mirror on the right click I am getting the "Properties" in SSMS????)

    What to do now?

    Should I restore a new transaction log with recovery?

    DB 1.5 Gb

    Log . 5 Gb

    Any clue,

    Thanks,

    DOm


    System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager



    • Edited by Felyjos Wednesday, November 5, 2014 6:05 PM
    Wednesday, November 5, 2014 6:00 PM

Answers

  • Do the following ...

    1. If there is any existing mirring session for this SRRS database then run this query to remove :

    ALTER DATABASE SRRS SET PARTNER OFF

    2. Recover the principal database using :

    RESTORE DATABASE SRRS WITH RECOVERY;

    3. Take a Full & Tlog backups

    4. Restore them in Mirror server with NORECOVERY

    5. Configure and enable the mirroring.. please follow the below link..

    http://www.mssqltips.com/sqlservertip/2464/configure-sql-server-database-mirroring-using-ssms/


    Raju Rasagounder Sr MSSQL DBA

    • Marked as answer by Felyjos Wednesday, November 12, 2014 4:51 PM
    Wednesday, November 5, 2014 9:21 PM

All replies

  • Are you setting up a new database mirroring?

    Principal should be available and online, mirror should be in no recovery state.

    --Prashanth

    Wednesday, November 5, 2014 6:59 PM
  • Are you setting up a new database mirroring?

    Principal should be available and online, mirror should be in no recovery state.

    --Prashanth

    I have also performed the same activity and my details is available in ayyached link.

    https://social.msdn.microsoft.com/Forums/en-US/sqlkjmanageability/thread/40e8f82f-09d8-4e4b-9dfe-e1384b333c73/#40e8f82f-09d8-4e4b-9dfe-e1384b333c73

    Regards.

    Wednesday, November 5, 2014 7:06 PM
  • Yes it is a new database mirroring nut on database restored from other servers...

    So it is the restoration which is not ending !!

    Dom


    System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager

    Wednesday, November 5, 2014 7:09 PM
  • I could not start the mirroring setup yet as the restoration is still not ending !!!

    "Restoring" 

    what is the last step after restoring the database in Full No Recovery and the transaction log with No Recovery as well... what is the step which will place the database online ready before the mirroring?

    Thanks,

    Dom


    System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager

    Wednesday, November 5, 2014 7:11 PM
  • Recover the principal database and start configuring the mirroring.

    restore database SRRS with recovery
    --Prashanth

    Wednesday, November 5, 2014 7:24 PM
  • this against the recommandations from Microsoft or is it only for the mirror DB?

    Create the mirror database by restoring the following backups, using RESTORE WITH NORECOVERY on every restore operation

    http://msdn.microsoft.com/en-us/library/ms190941.aspx

    -- Dom


    System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager


    • Edited by Felyjos Wednesday, November 5, 2014 8:22 PM
    Wednesday, November 5, 2014 8:21 PM
  • here you go...

    Restore FullBackup in mirror server with NoRecovery

    Restore Tlog Backup in mirror server with NoRecovery

    Then.... enable the database mirroring between Server A and Server B... Follow the below link..

    http://www.mssqltips.com/sqlservertip/2464/configure-sql-server-database-mirroring-using-ssms/


    Raju Rasagounder Sr MSSQL DBA

    Wednesday, November 5, 2014 8:55 PM
  • I could not pass the step2 as the database remains in "Restoring" mode on the principal and the Mirroring is not available

    - Dom 


    System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager

    Wednesday, November 5, 2014 9:02 PM
  • Try to remove the mirror...

    To remove database mirroring

    1. Connect to either partner.

    2. Issue the following Transact-SQL statement:

      ALTER DATABASE <database_name> SET PARTNER OFF
      


    Raju Rasagounder Sr MSSQL DBA

    Wednesday, November 5, 2014 9:12 PM
  • Then try to recover the primary database using the below sql code..

    RESTORE DATABASE <database_name> WITH RECOVERY;


    Raju Rasagounder Sr MSSQL DBA

    Wednesday, November 5, 2014 9:13 PM
  • Dom,

    If you use norecovery option, the database will be restoring state all the time. You need to bring your principal database online. Execute the below sql

    restore database SRRS with recovery

    Once you recover the principal database, you can go ahead and configure the mirror.

    --Prashanth


    Wednesday, November 5, 2014 9:14 PM
  • Do the following ...

    1. If there is any existing mirring session for this SRRS database then run this query to remove :

    ALTER DATABASE SRRS SET PARTNER OFF

    2. Recover the principal database using :

    RESTORE DATABASE SRRS WITH RECOVERY;

    3. Take a Full & Tlog backups

    4. Restore them in Mirror server with NORECOVERY

    5. Configure and enable the mirroring.. please follow the below link..

    http://www.mssqltips.com/sqlservertip/2464/configure-sql-server-database-mirroring-using-ssms/


    Raju Rasagounder Sr MSSQL DBA

    • Marked as answer by Felyjos Wednesday, November 12, 2014 4:51 PM
    Wednesday, November 5, 2014 9:21 PM