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