Database Development

How to find the last data backup on secondary replica?

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 >
   < diff backup >

Probably I’m missing something very simple.


Leave a Reply

Your email address will not be published. Required fields are marked *