December 3, 2009 at 12:39 pm
i'm in the process of customizing a backup monitoring system from one of the articles here.
http://www.sqlservercentral.com/articles/Backup/66564/
i'm creating my own reports to alert me if a backup fails or a database is not backing up.
part of the schema in the article was a table to hold the server and database names that are excluded from being reported. it's a 2 column table with the server name and database name.
My query to grab the data for any database that hasn't had a full backup in 7 days looks something like this. i added the join today. only problem is i keep getting data that matches up with what i have in the exclusion table called db_exclude_dim. when i tried using and after the where it excluded based on the server_name column which was no good. reason is some of those servers have databases that no longer exist but come out on the report. the goal is to have a clean report that we can show to the SOX auditors next year as part of our monitoring system
select b.server_name as Server_Name, b.dbname as Database_Name, max(b.backup_finish_date) as Backup_End_Time, Backup_Type =
case b.type
when 'D' then 'Full backup'
when 'I' then 'Differential Backup'
when 'L' then 'Transaction Log Backup'
when 'F' then 'filegroup Backup'
when 'G' then 'Differential File backup'
else 'See Books Online'
end
from backupset_dim b
left join db_exclude_dim e
on e.server_name = b.server_name and e.dbname = b.dbname
where b.dbname not in ('master', 'msdb', 'ReportServer', 'ReportServerTempDB', 'pubs', 'Northwind', 'model')
--and dbname not in (select dbname from db_exclude_dim)
--and server_name not in (select server_name from db_exclude_dim)
and Type = 'D'
and backup_finish_date > '2009-01-01'
and b.server_name not in ('xxx', 'xxx', 'xxx', 'sxxx', 'xxx', 'xxx', 'xxx', 'xxx', 'xxx')
group by b.dbname, b.server_name, b.Type
having max(b.backup_finish_date) < getdate() -7
order by b.server_name, database_name
December 3, 2009 at 12:43 pm
Add "and e.server_name is null" to your Where clause.
Left join will hook the data up, but then you need to exclude the ones that have a match.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 3, 2009 at 12:51 pm
thx
Looks like it worked
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply