March 21, 2018 at 3:18 am
Hi all
This is a t-sql question but also involves some knowledge of the AlwaysOn DMVs so wasn't sure where to best to post this. I have a daily check that reports on databases that have not been backed up within a certain time frame (or have never been backed up). Works well. We have now introduced alwayson availability groups and my daily check is telling me about databases not recently backed up even on my secondary replica - which i don't care about.
Here's my code:
DECLARE @MyDate DATETIME = '20 March 2018';
WITH LastBackupCTE (DbName, BackupfinishDate)
AS
(
SELECT database_name,
MAX(backup_finish_date) AS BackupFinishDate
FROM msdb.dbo.backupset bs
inner join msdb.dbo.backupmediafamily bmf
on bs.media_set_id = bmf.media_set_id
WHERE type = 'D' -- database backup.
GROUP BY database_name
)
SELECT d.name
FROM sys.databases d
LEFT OUTER JOIN LastBackupCTE f
ON d.name = f.DbName
WHERE (
f.BackupFinishDate IS NULL -- never backedup
OR
F.BackupFinishDate < @MyDate
)
AND d.name not in ('tempdb', 'ReportServerTempDb', 'pubs','northwind', 'AdventureWorks')
AND d.state_desc = 'ONLINE'
AND (sys.fn_hadr_is_primary_replica(d.name)) = 1
order BY d.name;
Currently this will report backup any databases not backed up recently (or ever) but only if they are part of an availability group. It will ignore databases which are NOT in an availability group, which is not what i want. I want to know about all databases, and if they are in an availability group, only tell me about those on the primary replica. T-sql isn't my speciality so bear with me.
Any help would be appreciated!
Thanks
Doodles
March 21, 2018 at 3:46 am
Try dmv sys.dm_hadr_database_replica_states and just join them:
DECLARE @MyDate DATETIME = '21 March 2018';
WITH LastBackupCTE (DbName, BackupfinishDate)
AS
(
SELECT database_name,
MAX(backup_finish_date) AS BackupFinishDate
FROM msdb.dbo.backupset bs
inner join msdb.dbo.backupmediafamily bmf
on bs.media_set_id = bmf.media_set_id
WHERE type = 'D' -- database backup.
GROUP BY database_name
)
SELECT d.name
FROM sys.databases d
left outer join sys.dm_hadr_database_replica_states ha on d.database_id=ha.database_id
LEFT OUTER JOIN LastBackupCTE f
ON d.name = f.DbName
WHERE (
f.BackupFinishDate IS NULL -- never backedup
OR
F.BackupFinishDate < @MyDate
)
AND d.name not in ('tempdb', 'ReportServerTempDb', 'pubs','northwind', 'AdventureWorks')
AND d.state_desc = 'ONLINE' and (ha.is_primary_replica=1 or ha.is_primary_replica is null)
--AND (sys.fn_hadr_is_primary_replica(d.name)) = 1
order BY d.name;
March 21, 2018 at 10:19 am
That's awesome! And so simple... sometimes the simple things are so elusive 🙂
Thanks for your quick response!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply