none
can not start mirroring sql 2017 RRS feed

  • Question

  • Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)   Aug 22 2017 17:04:49   Copyright (C) 2017 Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor) 

    when I attempt to start the mirroring, it fails because the failover db is restoring which is what is supposed to be after I restored a full and a transactional backups

    TITLE: Database Properties
    ------------------------------

    An error occurred while starting mirroring.

    ------------------------------
    ADDITIONAL INFORMATION:

    Alter failed for Database 'Stockmgmt'.  (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17199.0+((SSMS_Rel).171004-0254)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

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

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

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

    Database 'Stockmgmt' cannot be opened. It is in the middle of a restore. (Microsoft SQL Server, Error: 927)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.1000&EvtSrc=MSSQLServer&EvtID=927&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------


    ???


    • Edited by berli Monday, September 9, 2019 7:37 AM
    Monday, September 9, 2019 7:36 AM

Answers

  • Hi, there is a bug in the GUI which tries to run the command from the restoring database and not the master database.

    You'll need to try complete the process manually from the master database.

    1.) On the Mirror use the following to set the principal as a partner (use correct server name/port):
    ALTER DATABASE [Stockmgmt] SET PARTNER = 'TCP://enterprincipalsname:5022';
     
    2.) On the Principal use the following to set the mirror as a partner (use correct the server name/port):
    ALTER DATABASE [Stockmgmt] SET PARTNER = 'TCP://enterpartnerssname:5022';

    • Marked as answer by berli Wednesday, September 11, 2019 7:17 AM
    Monday, September 9, 2019 7:16 PM
  • Hi berli,

     

    >>Database 'Stockmgmt' cannot be opened. It is in the middle of a restore. (Microsoft SQL Server, Error: 927)

     

    What version of SSMS are you using? Please try to use the newest version of ssms 18.2: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017#download-ssms-182

     

    As a workaround, you can use TSQL commands. Before executing these commands, you should have Full and log backup restore on DR with no recovery.

     

    Mirror Server:

    USE MASTER; GO ALTER DATABASE SET PARTNER = 'TCP://PrincipalServer:5022' GO

    Principal server:

    USE MASTER; GO ALTER DATABASE SET PARTNER = 'TCP://MirrorServer:5022' GO

     

    Best regards,

    Dedmon Dai


    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

    • Marked as answer by berli Wednesday, September 11, 2019 7:20 AM
    Tuesday, September 10, 2019 8:27 AM
  • I found that the endpoint was on port 7022 and I was setting the partner on port 5022. I changed the endpoint to 5022 and the mirror came online.

    the command for the witness is

    ALTER DATABASE [Stockmgmt] SET WITNESS= 'TCP://WITNESSsql02:5022


    ???

    • Marked as answer by berli Wednesday, September 11, 2019 7:19 AM
    Wednesday, September 11, 2019 7:19 AM

All replies

  • Database 'Stockmgmt' cannot be opened. It is in the middle of a restore. (Microsoft SQL Server, Error: 927)

    And in which state is that database currently? Is it may be already subscriber in a mirroring and therefore in restore state?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, September 9, 2019 11:34 AM
  • new server. I just copied the db to one server, did a backup, and restored it with recovery so that it is in the restoring state, and am now trying to bring up mirroring to it

    ???

    Monday, September 9, 2019 12:11 PM
  • Hi, there is a bug in the GUI which tries to run the command from the restoring database and not the master database.

    You'll need to try complete the process manually from the master database.

    1.) On the Mirror use the following to set the principal as a partner (use correct server name/port):
    ALTER DATABASE [Stockmgmt] SET PARTNER = 'TCP://enterprincipalsname:5022';
     
    2.) On the Principal use the following to set the mirror as a partner (use correct the server name/port):
    ALTER DATABASE [Stockmgmt] SET PARTNER = 'TCP://enterpartnerssname:5022';

    • Marked as answer by berli Wednesday, September 11, 2019 7:17 AM
    Monday, September 9, 2019 7:16 PM
  • Hi berli,

     

    >>Database 'Stockmgmt' cannot be opened. It is in the middle of a restore. (Microsoft SQL Server, Error: 927)

     

    What version of SSMS are you using? Please try to use the newest version of ssms 18.2: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017#download-ssms-182

     

    As a workaround, you can use TSQL commands. Before executing these commands, you should have Full and log backup restore on DR with no recovery.

     

    Mirror Server:

    USE MASTER; GO ALTER DATABASE SET PARTNER = 'TCP://PrincipalServer:5022' GO

    Principal server:

    USE MASTER; GO ALTER DATABASE SET PARTNER = 'TCP://MirrorServer:5022' GO

     

    Best regards,

    Dedmon Dai


    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

    • Marked as answer by berli Wednesday, September 11, 2019 7:20 AM
    Tuesday, September 10, 2019 8:27 AM
  • the alter database on the mirror (prodsql02) completed successfully

    ALTER DATABASE [Stockmgmt] SET PARTNER = 'TCP://prodsql01:5022';

    on the primary (prodsql01) I get an error

    sg 1418, Level 16, State 1, Line 1
    The server network address "TCP://prodsql02:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

    ALTER DATABASE [Stockmgmt] SET PARTNER = 'TCP://prodsql02:5022';

    also how would I add a witness ?


    ???

    Tuesday, September 10, 2019 8:29 AM
  • what version of ssms does not have this bug?

    ???

    Tuesday, September 10, 2019 8:30 AM
  • I found that the endpoint was on port 7022 and I was setting the partner on port 5022. I changed the endpoint to 5022 and the mirror came online.

    the command for the witness is

    ALTER DATABASE [Stockmgmt] SET WITNESS= 'TCP://WITNESSsql02:5022


    ???

    • Marked as answer by berli Wednesday, September 11, 2019 7:19 AM
    Wednesday, September 11, 2019 7:19 AM