|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Monday, June 17, 2013 8:19 AM
Points: 36,
Visits: 146
|
|
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:
Server database_name last_db_backup_date Age backup_type SERVER1 DB1 2013-03-09 18:32:50.000 11 Database SERVER1 DB2 2013-03-09 13:03:16.000 11 Database SERVER1 DB1 2013-03-09 14:31:14.000 11 Database SERVER2 DB2 2013-03-09 12:56:19.000 11 Database SERVER2 DB3 2013-03-09 13:30:17.000 11 Database
(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
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Monday, June 17, 2013 8:19 AM
Points: 36,
Visits: 146
|
|
Got it... Added:
AND bh.Server NOT IN (SELECT rel.Server FROM ReportExclusionList rel WHERE bh.Server = rel.Server AND bh.database_name = rel.DatabaseName) AND bh.database_name NOT IN (SELECT rel.Server FROM ReportExclusionList rel WHERE bh.Server = rel.Server AND bh.database_name = rel.DatabaseName)
Works as expected now...
|
|
|
|