none
Database still in recovery state during database mirroring setup

    Question

  • When setting up database mirroring, I am getting error that the database cannot be opened. It is in recovering state. I followed the steps to setup mirroring which says to restore database with no recovery. If I restore database with recovery, I get another error saying that the server does not exist or cannot be reached. How to solve this issue.
    Sunday, February 10, 2019 4:12 PM

Answers


  • I am executing the mirroring setup as a domain user account. which is also the logon for sql service on both the primary and mirror server. dbname is the database name. Do I need to assign any permission to the domain user account. If so how and what permission?

    I can think of the following suggestions:

    1. Make sure you have granted "CONNECT" permission to the service account on the endpoints you're using in your mirroring configuration. 

    2. Make sure you're invoking the mirroring setup from the principal server. 

    3. Use an older version of SSMS. Per this thread, it seems this issue occurs in the newer SSMS version. 

    4. Use T-SQL to set up mirroring if #3 is not something you like. 

    My thought is yours is most likely the ssms version case wherein #3 above should help. Give it a try. 


    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.

    Monday, February 11, 2019 12:35 PM

All replies

  • Hi Rijo,

    Can you bring the exact error message/s?

    In the meantime, have you tried to set it up via T-SQL commands? See this guide. Also youtube is plenty of tutorials for seeting up mirroring.

    That said, I would recommend you to go with AlwaysOn Avalibility Groups, that is the evolution of the old mirroring.

    Hope that helps!

    Sunday, February 10, 2019 7:44 PM
  • When setting up database mirroring, I am getting error that the database cannot be opened. It is in recovering state. 

    That's just the way to do it. I mean, when you configure Mirroring, you'd have to restore a full backup of the principal DB on to the Mirror server (with NORECOVERY) followed by a transaction log backup restore (unless the DB is new), again with NO RECOVERY. You then right-click the DB and go to Mirroring page to configure Mirroring by following the wizard. Post the complete error message that you're seeing. 



    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.

    Monday, February 11, 2019 4:12 AM
  • This is the error I am getting.

    Alter failed for Database 'dbname'. (Microsoft sqlserver.smo)

    An exception occured while executing a Transact sql statement or batch

    (Microsoft.sqlserver.connectioninfo)

    Database 'dbname' cannot be opened. It is in the middle of a restore. (Microsoft sql server, Error:927)

    I am executing the mirroring setup as a domain user account. which is also the logon for sql service on both the primary and mirror server. dbname is the database name. Do I need to assign any permission to the domain user account. If so how and what permission?

    Monday, February 11, 2019 7:02 AM
  • Followed the same thing. Still I am getting this error.

    This is the error I am getting.

    Alter failed for Database 'dbname'. (Microsoft sqlserver.smo)

    An exception occured while executing a Transact sql statement or batch

    (Microsoft.sqlserver.connectioninfo)

    Database 'dbname' cannot be opened. It is in the middle of a restore. (Microsoft sql server, Error:927)


    Monday, February 11, 2019 7:04 AM

  • I am executing the mirroring setup as a domain user account. which is also the logon for sql service on both the primary and mirror server. dbname is the database name. Do I need to assign any permission to the domain user account. If so how and what permission?

    I can think of the following suggestions:

    1. Make sure you have granted "CONNECT" permission to the service account on the endpoints you're using in your mirroring configuration. 

    2. Make sure you're invoking the mirroring setup from the principal server. 

    3. Use an older version of SSMS. Per this thread, it seems this issue occurs in the newer SSMS version. 

    4. Use T-SQL to set up mirroring if #3 is not something you like. 

    My thought is yours is most likely the ssms version case wherein #3 above should help. Give it a try. 


    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.

    Monday, February 11, 2019 12:35 PM