This article about, How to resolve Microsoft SQL Error 1478? or The mirror database has insufficient transaction log data to preserve the log backup chain of the principal database.
What is Microsoft SQL Availability Group?
SQL Server always on availability group is one of the best high availability method in SQL server. This keep your databases available in case of disaster. You can setup multiple replica in SQL Server availability group which help to keep your production databases online in terms of disaster.
To add databases in availability group you need restore database full backup and transaction log backup on secondary replica then add database in availability group.
What is Microsoft SQL Error 1478?
Some time when you try to add database in AG group using automatic seeding option and database size is huge like 500+ GB then it get failed and database could not able join ag group and database goes in to recovery mode.
When you try to join database again then you will get below error.
(Microsoft SQL Server, Error 1478): The mirror database has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been restored on the mirror database.
This error occurred because whenever database was auto seeding data and it stopped during that period data has been changed on production database and it has different LSN. When it tries to apply that it mismatch on secondary as both LSN does not match due log is too recent too apply on secondary server.
How to resolve Microsoft SQL Error 1478?
To resolve (Microsoft SQL Server, Error 1478): The mirror database has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been restored on the mirror database.
Step 1 : Stop T log backup for database you want to add in HA.
Step 2 :Take Full backup and immediate transaction log backup on primary server.
Step 3 : Copy backup files on secondary replica.
Step 4 : Restore full backup on all secondary replica with no recovery.
Step 5 : Restore transaction log backup on all replica with no recovery.
Step 6 : Join database to availability group and verify sync status in HA dashboard.