Sql-server – Can’t set up mirroring for a specific database

mirroringsql serversql-server-2008-r2

We have several databases. All of them are mirrored. Until now, we never had issues with this.

One database had some problems, and now when we try to enable mirror again, it just stops. Message in the SQL Log is Database mirroring has been terminated for database 'databasename'.

What we do:

  - Create full backup of the primary database
  - Create backup of the transaction log, in the same backup file
  - Restore that backup on the secondary location, with the norecovery option
  - Set partner on mirror, then primary.

The primary gets status 'Principal, synchronizing' for a moment, but then it just stops and terminates the mirroring. Placing the mirror back in restoring state and the principal just as a database with no mirror.

What can we do to investigate what is going wrong here? I think the endpoints are fine, because the other databases have no problem at all.

Thanks for any advices.

Best Answer

  • Create full backup of the primary database
  • Create backup of the transaction log, in the same backup file
  • Restore that backup on the secondary location, with the norecovery option
  • Set partner on mirror, then primary.

When the mirroring session is established the primary must have the current LSN of the mirror-to-be in the active log. This is necessary so that the primary can start sending log records to the mirror to bring it up to date, w/o any interruption.

In your sequence of events you have two LSNs saved: one after the full backup (LSN1), one after the log backup (LSN2). The primary will be able to accommodate a mirror that is at LSN2, but not one at LSN1. If you restore the full backup with no-recovery and then also restore the log, also with no-recovery, that should end up with a proper mirroring candidate database. But if you only restore the full backup then the mirror will be at LSN1 and the primary will reject it.

Related Question