SQL Server – Backup History Using Availability Groups

availability-groupssql server

When using availability groups it is hard to get backup history in situation of failover to secondary node. It would good to have msdb backup set data to be in-sync with all the servers involved in availability groups. can i create a procedure or script to have the backup history information for all the backups from different msdb databases to a central database ? What is the best way to do this ?

Best Answer

We made a SSIS package that collect msdb history from each instance and centralize it on a "DBA" database. We then "purge" the local msdb from each instance.

That way, we can query the centralize DB for any backup history and it prevent every msdb from getting to big.