Avamar: SQL Incremental backups are promoted to FULL after upgrading the SQL plug-in from 7.4 to 7.5

INCREMENTAL backups are being promoted to FULL after upgrading the Avamar SQL plug-in to 7.5 SP1, the

The same INCREMENTAL backups, with the same dataset parameters, were not promoted to FULL on plug-in version 7.4 or older.

There are couples of Databases affected by this problem as shown in the snippet below.

avsql Warning <7420>: Forcing FULL backup for database '(local)/XXXXX' due to the following reason: A log gap was identified or a full backup was not found.

The SQL plug-in procedure to obtain the last Log Sequence Number (LSN) info of the database was modified from version 7.4 to 7.5:

In v.7.4, the sys.database_recovery_status database was used to generate the metadata, however in certain scenarios updating the last_log_backup_lsn in sys.database_recovery_status was time consuming.

Therefore, from version 7.5 the msdb..backupset database is being used instead to get last_lsn for the last incremental backup of the database.

• The 7.4 SQL query was:       

 "SELECT last_log_backup_lsn FROM sys.database_recovery_status WHERE database_id = DB_ID(N'%s')"

• The new 7.5 SQL query is:          

 "SELECT top 1 last_lsn as last_log_backup_lsn FROM msdb..backupset WHERE database_name=N'%s' AND type LIKE 'L' ORDER by last_lsn DESC" 

When a database is restored to the same SQL server, the wrong LSN number will be selected when running the next INCREMENTAL backup for that database. This is the result of the Avamar software code change on Avamar software version 7.5.

To resolve this issue, upgrade the Avamar Server to the following Avamar software release:

• 19.4.100-124 or later

The fix consists on a code change where the SQL statement that queries the msdb backup set table orders results by:

• "backup finish date"
• "last LSN."

"SELECT top 1 last_lsn as last_log_backup_lsn FROM msdb..backupset WHERE database_name=N'<DBNAME>' AND type LIKE 'L' ORDER by backup_finish_date DESC, last_lsn DESC"