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%'
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.