March 20, 2013 at 7:38 am
I have a metrics database and a few reports running from it.
One of them is to report on databases that haven't had a full backup run in the last week...
It checks other tables for Production and Online dbs etc etc...
My query looks something like:
SELECT bh.Server AS Server
,bh.database_name
,MAX(bh.backup_finish_date) AS last_db_backup_date
,datediff(dd,MAX(bh.backup_finish_date),Getdate()) AS Age
,bh.backup_type
FROM Backup_History bh
INNER JOIN ServerList_SSIS sl
ON bh.Server = sl.ServerDisplayName
AND sl.Environment = 'PROD'
AND sl.BackupReports = 'Y'
INNER JOIN Database_Info dbi
ON bh.Server = dbi.Server
AND bh.database_name = dbi.DatabaseName
AND dbi.Status <> 'OFFLINE'
WHERE bh.backup_type = 'Database'
GROUP BY bh.Server
,bh.database_name
,bh.backup_type
HAVING (MAX(bh.backup_finish_date) < DATEADD(dd, - 8, GETDATE()))
ORDER BY Age DESC
,bh.Server
,bh.database_name
The output right now is:
Serverdatabase_namelast_db_backup_dateAgebackup_type
SERVER1 DB12013-03-09 18:32:50.00011Database
SERVER1 DB22013-03-09 13:03:16.00011Database
SERVER1 DB12013-03-09 14:31:14.00011Database
SERVER2 DB22013-03-09 12:56:19.00011Database
SERVER2 DB32013-03-09 13:30:17.00011Database
(Sorry about the formatting)
Now I need to exclude the DB1 database so I created an exclusion table with Server and DB columns and added that server and database.
I need help with the query to exclude that one from the report:
I've tried:
AND (bh.Server NOT IN(SELECT SERVER from ReportExclusionList)
AND bh.database_name NOT IN(SELECT DatabaseName FROM ReportExclusionList))
After the WHERE clause but it excludes all databases from the specified server...
I Just can't wrap my head around the sql... (Maybe change the NOT IN portion to include the database and server...)
Any help would be appreciated.
Thanks
March 20, 2013 at 7:45 am
Got it... Added:
AND bh.Server NOT IN
(SELECTrel.Server
FROMReportExclusionList rel
WHEREbh.Server = rel.Server
ANDbh.database_name = rel.DatabaseName)
AND bh.database_name NOT IN
(SELECTrel.Server
FROMReportExclusionList rel
WHEREbh.Server = rel.Server
ANDbh.database_name = rel.DatabaseName)
Works as expected now...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply