SQL Server – How to Find Last Data Backup on Secondary Replica

availability-groupsbackupsql server

To determine when database has been backed up the last time usually is enough to look into backup tables in msdb database.

However, it doesn't work well with Availability Groups – after the failover new active replica doesn't know when the last FULL\DIFF backup was taken because local msdb database doesn't have those records.

It is a bit easier with log backups – DBCC DBINFO (@DBName) WITH TABLERESULTS gives you the last LOG backup data no matter where it was taken.

My question is – is there an easy way to say on the new active replica, right after failover, when the last data backup was taken?
There is always an option to query each replicas through OPENROWSET (or anything like this), but this introduces unwanted complexity.

The goal is to make sure that code like this works stable even after the failover:

IF dbo.lastBackupWasTakenMoreThanSevenDaysAgo(@DatabaseName) = 1
   < full backup >
ELSE
   < diff backup >

Probably I'm missing something very simple.

Thanks!

Best Answer

The differential_base_time and lsn columns in sys.database_files indicate the point in time and in the log stream when the last non-copyonly backup was taken. So:

select min(differential_base_time) last_full_backup_utc, 
       datediff(second,min(differential_base_time),SYSUTCDATETIME())/( 24*60*60.) days_since_last_full_backup
from sys.database_files
where type_desc = 'ROWS'
and is_read_only = 0

If you have read-only filegroups a backup will not update the differential_base_time, so ignore those.

Related Question