Avamar: How to Use Avamar SQL Plug‑in T‑SQL Scripts During Troubleshooting

Generate Complete Database Backup History

Use this script to retrieve the full backup history for a database over a defined time period.
This script helps determine if and when a specific database was last backed up and the size of each backup.

1. Open SQL Server Management Studio.
2. Connect to the SQL Server instance.
3. Run the following T‑SQL query.

   SELECT msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_start_date,
   msdb.dbo.backupset.backup_size,
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.type,
   msdb.dbo.backupset.database_backup_lsn,
   msdb.dbo.backupset.first_lsn,
   msdb.dbo.backupset.last_lsn
   FROM msdb.dbo.backupmediafamily
   INNER JOIN msdb.dbo.backupset
   ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
   WHERE database_name IN ('sql_database_name_here')
   --and msdb.dbo.backupset.backup_finish_date between '1/20/2000' and '10/14/2022'
   ORDER BY 
   2 DESC,
   3 DESC

    
4. Replace sql_database_name_here with the actual database name.
5. Optionally enable the date range filter to narrow the results.

Validate SQL Log Sequence Number (LSN) Continuity

Use these scripts when Avamar SQL Plug‑in logs report log gap errors.
These queries help determine whether the SQL transaction log chain is broken.

1. Run the following query to list log backup LSN values.

   SELECT last_lsn, type, user_name FROM msdb..backupset WHERE database_name=N'sql_database_name_here' AND type LIKE 'L' ORDER by last_lsn DESC

2. Run the following query to retrieve the last log backup LSN recorded by SQL Server.

   SELECT last_log_backup_lsn FROM sys.database_recovery_status "WHERE database_id = DB_ID(N’sql_database_name_here')"

3. Replace all instances of sql_database_name_here with the database name being analyzed.
4. Compare the LSN values returned by these queries.

Additional Information / Notes

• Manual or third‑party SQL backups can break the log chain used by the Avamar SQL Plug‑in.
• Always verify SQL backup activity outside of Avamar during log gap investigations.
• LSN validation is critical before attempting log restores or GLR operations.