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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy