Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
Do you restore the log too, on the secondary? –  Remus Rusanu Mar 11 at 13:18

1 Answer 1

  • 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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