3

Recently I have started to implement database mirroring and faced with some trouble. According to Books online, full database backup and T-Log backup must be restored in the mirror instance in WITH NORECOVERY mode:

The mirror database must be in the RESTORING state for mirroring to work. When preparing a mirror database, you must use RESTORE WITH NORECOVERY for every restore operation. Minimally, you will need to restore WITH NORECOVERY a full backup of the principal database, followed by all subsequent log backups.

But when I start mirroring, I receive following exception:

"Database "MyDB" cannot be opened. It is on the middle of restore(Microsoft SQL Server, Error 927)

Can anyone explain, whether I misunderstood something ?

Improve this question
9
  • Could the restore possibly NOT be complete yet on the destination server?
    – Kris Gruttemeyer
    Commented Sep 2, 2016 at 13:39
  • But how can it be completed if I issued the last T-log restore in WITH NORECOVERY state?
    – Nizami Khalilbayli
    Commented Sep 2, 2016 at 13:41
  • So you restored the FULL WITH NORECOVERY then restored the LOG WITH NORECOVERY, right? Are you sure you didn't restore it to the primary accidentally (I've made that mistake on a DEV server before, that's why I ask).
    – Kris Gruttemeyer
    Commented Sep 2, 2016 at 13:42
  • No, I have restored both of them to secondary server
    – Nizami Khalilbayli
    Commented Sep 2, 2016 at 13:45
  • I think you are issuing the mirroring command on the wrong server. This is a really old blog of mine and was written for 2005, but it does apply for the newer versions too. It shows which server to run which command on. weblogs.sqlteam.com/tarad/archive/2007/02/13/60091.aspx
    – Tara Kizer
    Commented Sep 2, 2016 at 14:24

3 Answers 3

Reset to default
4

Create endpoints on all servers:

CREATE ENDPOINT EndPointName 
STATE=STARTED AS TCP(LISTENER_PORT = PortNumber, LISTENER_IP = ALL) 
FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, 
                   ENCRYPTION = REQUIRED ALGORITHM RC4)

For a witness server, change ROLE = PARTNER to ROLE = WITNESS in the witness's endpoint.

Run the first SET PARTNER command on the mirrored server:

ALTER DATABASE DatabaseName SET PARTNER = N'TCP://PrincipalServer:PortNumber'

Notice that it is pointing to the principal server in the command, but you are running it on the mirror.

Then run the next two commands on the principal server, pointing the SET PARTNER at the mirror:

ALTER DATABASE DatabaseName SET PARTNER = N'TCP://MirrorServer:PortNumber'
ALTER DATABASE DatabaseName SET SAFETY OFF --OFF=async, FULL=sync

If you are setting up a witness for automatic failovers, use SET SAFETY FULL above instead of SET SAFETY OFF and then run this command on the principal:

ALTER DATABASE DatabaseName SET WITNESS = N'TCP://WitnessServer:PortNumber'
Improve this answer
9
  • Yes I made sure that endpoint is existing on my witness server, bet when issuing Set witness command on my principal server I get an error: Database "X" is not configured for database mirroring. Why it could be so ?
    – Nizami Khalilbayli
    Commented Sep 2, 2016 at 15:34
  • Did you complete the other commands above? The commands on the principal and the secondary must be done before the witness.
    – Tara Kizer
    Commented Sep 2, 2016 at 15:41
  • Yes, of course, i issued it first on the mirror, then on the principal
    – Nizami Khalilbayli
    Commented Sep 2, 2016 at 15:42
  • Is mirroring working now (ignoring the witness for the moment)? Make sure they are synchronizing.
    – Tara Kizer
    Commented Sep 2, 2016 at 15:45
  • 1
    Yes do that and be sure that you specify WITH NORECOVERY on the mirror server for all backups you are restoring, including the last LOG backup. If you have a job on the principal server that is actively backing up the logs, you may want to pause that. If you don't pause it, you'll need to restore each of those backups too. If you do pause it, make sure you enable it after you are done! (That's a step I've forgotten far too many times.)
    – Tara Kizer
    Commented Sep 2, 2016 at 15:58
1

Please follow the below process to get rid of issue. If it is small Database it wont much time.

**Note : Below steps are to be followed in Secondary Server Only**
  • Delete the database.
  • Go to the data folder and delete any MDFs that look applicable to the database.
  • Go to the log folder and delete any LDFs that look applicable to the database.
  • Restore the database again(Fresh Copy of Primary Server Database) with NORECOVERY
  • Restore the T-Log(T-Log after the Full Backup of Primary Server Database) with NORECOVERY

  • Configure Database Mirroring

    • Also please check the Service pack and try to upgrade sql server to latest version.

Good luck!

Improve this answer
1
  • You do not need to delete the MDF or LDF files if you drop a database in restoring mode. That's only needed if you dropped an offline database.
    – Tara Kizer
    Commented Sep 2, 2016 at 16:44
-2

Here I go with this amazing workaround... - I have the same situation , the same errors... - I was working with SQL2016, as you know, it doesn't has Management Studio - Wiht the downloaded Management Studio of SQL2017 I was working - So moving and make the configuration of mirror with Management Studio of SQL2012 work with out problem !!!

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.