I have a SQL Server 2012 maintenance plan called 'backup databases'. This plan has two subplans 'weekly' and 'daily'. I would like a list of databases by ID associated with each of the subplans.
Or, more accurately, I would like to associate database ids with a subplan on a true/false basis.
I can query
msdb.dbo.sysmaint.. like so:
SELECT * FROM msdb.dbo.sysmaintplan_plans smp INNER JOIN msdb.dbo.sysmaintplan_subplans smsp ON smp.id = smsp.plan_id
But I don't see anywhere I can join to
master.sys.databases. Is there a way that I can do this?
I can see on this page here: https://technet.microsoft.com/en-us/library/ms188062(v=sql.110).aspx, reference to views that contain information on past backups. But I'm specifically looking for information on what databases are currently scheduled to be backed up.
I can find a list of backups in the
msdb.dbo.backupset table, however the backups are not associated to databases, but rather database_guids (https://msdn.microsoft.com/en-us/library/ms186299.aspx).
This is useful in that I know what databases have been backed up, but I would still like to know what databases are registered in the current maintenance plan to be backed up.