Good morning all,
I have a query that returns pivoted information on all FULL and Differential backups for my AAG SQL instances that works (so I thought) very well, but when the AAG toggles after the FULL backup is taken, the next day the query fails to pull the differential information. In short server1 primary had a full backup taken on Friday night by NetBackup then on Saturday the AAG toggles now having server2 as primary and NetBackup successfully performs a differential backup on it. My query though does not report on that backup having taken place. Attached is the code for your review, looking to see what I’m doing wrong. Thanks for guidance with this
SELECT
@@SERVERNAME,
M.name as DBName,
[RecoveryModel] = M.recovery_model_desc,
[State] = M.state_desc,
FORMAT(ISNULL(M.D, '19000101'), 'MM/dd/yyyy hh:mm') as LastFull ,
FORMAT(ISNULL(M.I, '19000101'), 'MM/dd/yyyy hh:mm') as LastDifferential,
M.Software as 'Backup_Software_info'
FROM
(
SELECT
db.name,
db.state_desc,
db.recovery_model_desc,
a.type,
a.backup_finish_date,
a.name as 'Software'
FROM master.sys.databases AS db
LEFT OUTER JOIN msdb.dbo.backupset AS a
ON a.database_name = db.name
) AS Sourcetable
PIVOT
(
MAX(backup_finish_date)
FOR type IN
(
I,
D
)
) AS M --MostRecentBackup
WHERE name NOT IN
( N'master', N'msdb', N'model', N'tempdb', N'STIGMonitor' ) --Ignore system databases
and
M.D > DATEADD(DAY, -7, GETDATE()) --Look back only 7 days of FullBackup
and
name in (SELECT name from sys.databases) --Active Databases
ORDER BY name