SQL Server 2016 _can't start mirror

Question

After upgrading SQL Server 2014 to SQL Server 2016 (both Enterprise editions), I can't start mirror.

This works normally on SQL Server 2014.

After I restore (WITH NORECOVERY) mirror database with full and log backups of primary database I normally run configure setup.

After click on Start mirror I get this error:

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

Answer 1

I had the same error message (and was using SSMS 2016 GUI), however the problem was solved by ensuring that all three instances (principal, mirror and witness) were running as the same (windows) service account.

Answer 2

Issue with SQL 2016 GUI. Its worked with TSQL commands.

Mirror Server:

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

Principal server:

USE MASTER;

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

before executing these commands, you should have Full and log backup restore on DR with no recovery.

Answer 3

This seems to be a bug in SSMS, after issueing full and log backups, and restoring them in NORECOVERY mode at the mirror instance side, try to execute the following commands:

In Mirror instance:

ALTER DATABASE <Database Name>
    SET PARTNER =   
    'TCP://<Your Principal Server>.<Domain>:<Mirroring Port>'  
GO  

In Principal instance:

ALTER DATABASE <Database Name>
    SET PARTNER =   
    'TCP://<Your Mirror Server>.<Domain>:<Mirroring Port>'  
GO  

After executing these commands your mirroring should be up and running. To check if every thing is configured you can check this query's output:

SELECT mirroring_state_desc 
FROM sys.database_mirroring
Where database_id = DB_ID('dbTest')

You can read more about different mirroring states values, here: https://learn.microsoft.com/en-us/sql/database-engine/database-mirroring/mirroring-states-sql-server?view=sql-server-ver15