Best Practices for Application-Aware Backups for Microsoft SQL Server
Transaction Log Backups Skipped for Simple Recovery Model Databases
If you configure application-aware backups for a SQL Server database that uses the simple recovery model, transaction log backups are skipped. As a workaround, you can use one of the following options:
- Reconfigure the database to use the full recovery model.
- Perform a full backup to ensure that the database is fully protected.
Backup Preference Configuration for AlwaysON
Set the primary replica as the backup preference so that there are complete backup cycles available for restores. This configuration option eliminates the following errors:
- Skipping database [AlwaysOn_DataBase]. Not a preferred replica to run backup.
The software displays this error when the replica is not set as the backup preference and the SQL Server Agent is set to recognize the backup preference setting of the availability groups. When the software attempts a backup operation of the replica, the databases that are part of the availability group are skipped.
- Skipping database [AlwaysOn_ DataBase]. Only BACKUP LOG is fully supported on secondary replicas. BACKUP DATABASE supports only copy-only full backups of the database, files, or file groups. Differential backups are not supported on secondary replicas.
The software displays this error because the secondary replicas support copy-only database backups. When the software attempts to back up databases that are a part of availability group on the secondary replica, the databases are not backed up.
- If transaction log backups are running on all replicas, and truncation happens during the backup on a secondary replica, the LSN chain on secondary replica breaks. This action cause the software to automatically attempt to convert the next transaction log backup to a full backup, which is not supported on secondary replicas.
Last modified: 12/10/2019 8:57:21 PM