I have a problem with create mirror database in SQLServer 2016.

Question

i'm using Management studio v 17.9.1

below steps i followed for creation:

1- All SQL services on Primary and Standby server are running with Domain user account,Domain is xxx.sql.com

2- This domain user account has sysadmin permission on both Servers

3- i took a full backup from primary and transaction log backup from the primary

4- Restore the full backup on standby machine with no recovery option

5- Restore transaction log backup with norecovery option

6- start mirror configuration from Primary and finish steps successfully

7- Screen showed with the following message:

标题: 数据库属性

---

开始镜像时出错。

---

其他信息:

数据库 "xxx" 的 更改 失败。 (Microsoft.SqlServer.Smo)

有关帮助信息，请单击: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17289.0+((SSMS_Rel_17_4).181117-0805)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=更改+Database&LinkId=20476

---

执行 Transact-SQL 语句或批处理时发生了异常。 (Microsoft.SqlServer.ConnectionInfo)

---

无法打开数据库 'xxx'。该数据库正在进行还原。 (Microsoft SQL Server，错误: 927)
(can not open database 'xxx',the database are restoring.)

有关帮助信息，请单击: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.5026&EvtSrc=MSSQLServer&EvtID=927&LinkId=20476

---

按钮:

确定

I need your support ASAP
Regards

Answer 1

Hi @Paul Constantine ,

>无法打开数据库 'xxx'。该数据库正在进行还原。 (Microsoft SQL Server，错误: 927)

This error can occur when you try to configure mirroring using the Configure Database Mirroring Security Wizard (SSMS UI). Please using below TSQL commands to resolve this issue. Please change the names of Mirror DB , primary server ,mirror server and the port number in below T-SQL command.

On mirror server, set the primary as the partner;

Use master   
GO  
ALTER DATABASE MirrorDB  
   SET PARTNER = ‘TCP://primaryserver.contoso.com:5022’  
Go  

On primary server, set the mirror as a partner;

Use master   
GO  
ALTER DATABASE MirrorDB  
   SET PARTNER = ‘TCP://Mirrorserver.contoso.com:5022’  
Go  

Please refer to MS document Using T-SQL configure database mirroring or this blog Database cannot be opened. It is in the middle of a restore. (Error 927) to get more information.

Best regards,
Cathy Ji

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi
Database Mirroring in SQL Server is deployed as a method to increase the availability of a SQL Server database in the event of a disaster. It can only be implemented in databases that have full recovery model and works on per-database basis. In database mirroring, two copies of a single SQL Server database are stored on different servers that are separated physically. While the Primary Server provides database to the clients, the Mirror Server acts as a standby server that takes over the place of physical server in case any accident occurs.
link address:https://www.sqlservercentral.com

Best Regards.

Please click the Mark as answer button and vote as helpful if this reply solves your problem.