August 13, 2013 at 2:53 am
SQL 2012 AlwaysOn High Availability, how to Query last backup history from MSDB with 3 replicas
Data Info for my "high availability system" build SQL2012 :
OS2008R2
SQL2012, with AlwaysON enabled
Number of replicas, 3
Backup Method:
NO backup on the primary during working hours, but FULL and DIFF outside working hours.
LOG backup on Primary each 15 min.
Scenario:
Each 30min I check for a successful LOGbackup from MSDB
Challenge:
-no matter what state my replicas are in, I need the complete BackupList from
all replica's MSDB to see (FULL,DIFF,LOG) backup_start_date and backup_finish_date
I could Query MSDB from each replica, but if one replica are Down cause of hw/sw
failure or other tings, I still need the last full history from that replicas msdb backup list.
One Solution:
Query the msdb.dbo.backupset to DW location or to the other replicas in the availability Group.
Any ideas?
Regards
Michael Hesselberg
August 25, 2013 at 9:50 am
Yes, unfortunately you have to manually combine the msdb backup history data across all replicas. Which one can actually include a backup is statet in sys.availability_replicas
Andreas
---------------------------------------------------
MVP SQL Server
Microsoft Certified Master SQL Server 2008
Microsoft Certified Solutions Master Data Platform, SQL Server 2012
www.insidesql.org/blogs/andreaswolter
www.andreas-wolter.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply