SQL SERVER – Mirroring Error 1412 – The remote copy of database has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error:)

With mirroring gone as deprecated feature, I have tried to keep away from exploring into what has been happening in this space. But what can I do when customers still use them? The challenges for consulting is pushing the boundaries of my comfort zone here. This made me revisit some of my learnings again. As part of this exercise, I learnt a minute or two which I personally felt was worth a share as part of this blog post. Here is a story for Mirroring Error 1412.

While one of my client was trying to configure mirroring, here was the message received when they click on “Start Mirroring” in the wizard.

SQL SERVER - Mirroring Error 1412 - The remote copy of database has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error:) mirror-err-01

Here is the text of the message.

Solarwinds

TITLE: Database Properties
——————————
An error occurred while starting mirroring.
——————————
ADDITIONAL INFORMATION:
Alter failed for Database ‘MirrorTest’.  (Microsoft.SqlServer.Smo)
——————————
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
The remote copy of database “MirrorTest” has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error: 1412)
——————————
BUTTONS:
OK
——————————

If you are seasoned DBA, the error message would make sense. Basically there is some information which is supposed to be there in LDF file is not there.

Here are the steps to reproduce the issue.

  1. Take Full backup of the database from principal server (F1.bak)
  2. Take Transaction log backup of the database from principal server (T1.trn)
  3. Restore F1.bak on mirror server with norecovery.
  4. Configure mirroring without restoring T1.trn

It means that we need to restore ALL transaction log backups on mirror server. So, if you are getting error mentioned in the blog, use below to find all backups taken of the database and make sure all are restored.

SQL SERVER – Get Database Backup History for a Single Database

In my client’s case they found a backup job which was taking log backup on regular basis. Once we restored all missing backups, we were able to configure mirroring without any error.

Have you encountered some error during Mirroring? Please share via comments and help others.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – The Need for Data Tier Elasticity in Cloud Computing
Next Post
SQL SERVER 2016 – STRING_SPLIT Function Performance Comparison – Notes from the Field #118

Related Posts

9 Comments. Leave new

  • Hi Pinal,

    You have been of immense help, your blogs help not just to resolve the issues but also to broaden our understanding and enriching the knowledge. BIG Thank you for that.

    We had a huge DB (>500 GB) configured for DBM. Due to some mis-configuration of net backup tool, Full back up job couldn’t complete in more than 12 hrs. Log file for Principal DB ran out of space and DBM went into suspended state.

    We had to stop the job and then tried resuming DBM but no luck. Had to take full and log backup of Primary DB, than restore them on Mirror DB and set up DBM all over again. As you would expect, it was Prod env and we had log back up in place.

    Just wanted to check if we could have taken ‘differential’ and then log back up and restore them. Does that work ?

    Appreciate your response. Thank you.

    Br,
    Anil

    Reply
    • Yes. Full + Diff + Log also works… you need to make sure that you restore last log backup taken from principal.

      Reply
      • hi.
        i keep getting these errors while setting up mirroring
        Error 1412, 1475, 1478

        i made sure that i restored full back and then transaction log backup and tried to configure mirroring. configuration goes successful but when i hit on start mirroring i get those above errors. what should i do??

  • baachi. check SQL Server errorlog

    Reply
    • error log only shows error: 1443, severity: 16, state: 2

      Reply
      • Check ERRORLOG on both sides.

        1443 = Database mirroring has been terminated for database ‘%.*ls’. This is an informational message only. No user action is required.

        You may need to capture profiler to get more details.

  • Te solution is to take a full backup and a tlog backup of the pricnipal database and then restored it with no recovery option… Then try to create again the Mirroring.

    Best Regards,
    Hiram Walker O.
    DBA

    Reply
  • We are facing the same error now, but we lost the previous log back ups. Hence we have taken Full Back Up, Log Back Up from Primary Server and restored into Mirror DB. When we try to add this secondary server into Availability group, still we are getting the same issue. How can we resolve if we lost the previous back ups.

    Reply
    • Hi Raju, This one is difficult to answer This is more of a consulting question. You may have to hire a consultant to resolve problems like such.

      Reply

Leave a Reply

Menu