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.



1 Answer 1

-- This seems to be close to I think what you are needing 
-- shows dbs not in a maint plan (run pointing to msdb)  
t1.name as 'db_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
  • thanks for your answer. However, my sysdbmaintplan_databases table is empty! [link]msdn.microsoft.com/en-us/library/ms190308(v=sql.110).aspx implies this a legacy table from pre-SQL2005. I have data in sysmaintplan_log, sysmaintplan_logdetail and sysmaintplan_subplans, but nothing in tables beginning sysdbmaintplan*. Any other thoughts? Sep 16, 2016 at 7:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.