How to findout is database not in maintenance plan | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

How to findout is database not in maintenance plan

Hi all
i have 10 server with 100 dbs.
i want find out which database is not in maintenance plan.
manual work is to hard.
please provide some scripts
Thanks
S. Ramesh
In SQL 2000, you could run this for each server: SELECT sd.* FROM master.dbo.sysdatabases sd
WHERE NOT EXISTS
(SELECT * FROM msdb.dbo.sysdbmaintplan_databases mpdb
WHERE mpdb.database_name = sd.[name])
AND sd.[name] NOT IN (‘master’, ‘model’, ‘msdb’, ‘tempdb’) But I guess in SQL 2005 you’ll need something different.
Hi
i got my self SELECT*
FROM master.dbo.sysdatabases SDB
WHERE NOT EXISTS
( SELECT SJS.command
FROM msdb.dbo.sysjobsteps SJS
WHERE SJS.command LIKE ‘%BACKUP%DATABASE%’ + SDB.name +’%’
) AND NOT EXISTS
(
SELECTSDBMP.database_name
FROM msdb.dbo.sysdbmaintplan_databases SDBMP
JOIN msdb.dbo.sysdbmaintplan_jobs SDBMPJ ON SDBMPJ.plan_id = SDBMP.plan_id
JOIN msdb.dbo.sysjobsteps SJS ON SJS.job_id = SDBMPJ.job_id
WHERE SJS.command LIKE ‘%-BkUpDB%’
AND SDBMP.database_name = CASE
WHEN SDB.name IN (‘master’,’model’,’msdb’) AND SDBMP.database_name = ‘All System Databases’
THEN ‘All System Databases’
ELSE SDB.name END
)
Thanks
S. Ramesh
]]>