Error taking log backups on AG secondary if no log backup is taken on primary
When creating a new database and using automatic seeding, it is possible to have a database participating in an Availability Group when no log backup has ever been taken.
Consider this scenario on a 2-node AG
* Create a database on the primary node.
* Backup database to disk.
* Create a new AG with this DB, using automatic seeding (or add DB to an existing AG with automatic seeding)
* Attempt to take a log backup on the secondary node.
* Log backup will end in error:
Msg 35294, Level 16, State 5, Line 1
Log backup for database "DatabaseName" on a secondary replica failed because a synchronization point could not be established on the primary database. Either locks could not be acquired on the primary database or the database is not operating as part of the availability replica. Check the database status in the SQL Server error log of the server instance that is hosting the current primary replica. If the primary database is participating in the availability group, retry the operation.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
This error will persist when attempting to take log backups on secondary.
To resolve, simply take a single log backup on the Primary DB.
SQL Server should either (1) block adding the DB to the AG until after an initial Log backup is taken [similar to the requirement that a FULL backup be taken], or (2) allow the above scenario to work without failure.
SQL2017 the same situation. After I restore all bases from taken from another server and place it under alwayson, maintanance backup transaction log failed with error: Log backup for database on a secondary replica failed because a synchronization point could not be established on the primary database. So, I switch preferables of backups to whatever server and took tr.log from primary node, and then switched back to secondary preferable
@Andy - What is the SQL version ?