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.
Here is the text of the message.
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.
- Take Full backup of the database from principal server (F1.bak)
- Take Transaction log backup of the database from principal server (T1.trn)
- Restore F1.bak on mirror server with norecovery.
- 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)
13 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
Yes. Full + Diff + Log also works… you need to make sure that you restore last log backup taken from principal.
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
error log only shows error: 1443, severity: 16, state: 2
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
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.
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.
Hi Panel,
we are facing the error1412, We stop the log back but all so we need to face the same error do you have any suggestion for this.
Please stop the Log backup job and take Differential backup and Tlog backup and restore into DR nodes.
Stop T logbackup job and Take Diff and Tlog and restore into DR nodes
Avinash Vazenepalli, Aditya!!!!!!
THANKYOU FROM MY HEART
and to people of the future reading this post and its comments,
the issue i was having was that by the time i had finished restoring the db, more new logs were taken, meaning i was out of date every time i tried!
i disable my log backup job -> now we are in sync -> can turn them back on again
Thankyou dear random poster, thankyou so much