Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help with query using exclusion table Expand / Collapse
Author
Message
Posted Wednesday, March 20, 2013 7:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 8:37 AM
Points: 49, Visits: 218
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
Post #1433206
Posted Wednesday, March 20, 2013 7:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 8:37 AM
Points: 49, Visits: 218
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...
Post #1433215
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse