Sql-server – List databases in Maintenance Plan

backupmaintenance-planssql server

I'm trying to list all databases in existing Maintenance Plans. (or, more importantly those NOT in a plan…)

I'm using the followed code, based on this blog:

Select DISTINCT p.name as 'Maintenance_Plan'
, RIGHT(smpld.line4,LEN(smpld.line4)-6) as 'Backup_Type'
, sp.subplan_name as 'Subplan_Name'
, smpld.line3 as 'Database_Names'
, job.name As 'Job_Name'
From msdb.dbo.sysjobs As job
join  msdb.dbo.sysmaintplan_subplans sp
on sp.job_id = job.job_id
inner join msdb.dbo.sysmaintplan_plans p
on p.id = sp.plan_id
join msdb.dbo.sysmaintplan_log smpl
on p.id = smpl.plan_id 
and sp.subplan_id =smpl.subplan_id
join msdb.dbo.sysmaintplan_logdetail smpld
on smpl.task_detail_id=smpld.task_detail_id 
where job.[enabled] = 1
and smpld.line3<>''
and smpld.line4 like '%Full%'

However, sysmaintplan_logdetail.line3 is only a nvarchar(256) so doesn't store the complete list and I'm not sure what use this column is! It's also comma separated and not easy to interrogate.
There is a Command column in sysmaintplan_logdetail. This is nvarchar(max) and does indeed contain all the detail of the maintenance plan. However it would take a lot of text manipulation to extract just a list of databases. Typical contents:

BACKUP DATABASE [SP2013_Enterprise_Search_LinksStore] TO  DISK = N''\\uk-sba-sql02\backups\SP2013_Enterprise_Search_LinksStore_backup_2015_11_16_113747_3942470.bak'' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N''SP2013_Enterprise_Search_LinksStore_backup_2015_11_16_113747_3942470'', SKIP, REWIND, NOUNLOAD,  STATS = 10

Can anyone suggest a simple robust way of extracting database details from maintenance plans? I'm on SQL Server 2012. I normally use the Ola Hallengren Maintenance Solution, but I've inherited these.

Thanks

Ian

Best Answer

-- This seems to be close to I think what you are needing 
-- shows dbs not in a maint plan (run pointing to msdb)  
select
t1.name as 'db_name',
--t2.database_name,
t1.crdate as 'date_created'
FROM [master].[dbo].[sysdatabases] as t1
left outer join
dbo.sysdbmaintplan_databases as t2
on t1.name = t2.database_name
where t2.database_name is null
and t1.name not in ('tempdb','msdb','model','master')
order by crdate desc
Related Question