As a SQL Server consultant, I often come across various challenges when working with clients who still use deprecated features like mirroring. While mirroring may no longer be a recommended solution, it is important to understand and troubleshoot any errors that may arise during the configuration process.
In this blog post, I want to share a specific error that I recently encountered – Mirroring Error 1412. This error occurs when attempting to start mirroring and is accompanied by the following 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 an experienced DBA, you may already have an idea of what this error means. Essentially, it indicates that there is missing information in the LDF file. Let’s take a look at the steps to reproduce this issue:
- Take a full backup of the database from the principal server (F1.bak)
- Take a transaction log backup of the database from the principal server (T1.trn)
- Restore F1.bak on the mirror server with the
NORECOVERYoption - Configure mirroring without restoring T1.trn
The key point to note here is that we need to restore all transaction log backups on the mirror server. If any backups are missing, you may encounter the error mentioned above.
To ensure that all backups are restored, you can use the following query to retrieve the backup history for a single database:
SELECT * FROM msdb.dbo.backupset WHERE database_name = 'YourDatabaseName' ORDER BY backup_finish_date DESC;
In one of my client’s cases, we discovered that a backup job was regularly taking log backups. By restoring all the missing backups, we were able to successfully configure mirroring without any errors.
If you have encountered any errors during the mirroring process, I encourage you to share your experiences in the comments section below. By sharing our knowledge, we can help others overcome similar challenges.