Sql-server – Select maintenance subplans via database_id

sql serversql-server-2012

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.

Best Answer

Maintenance Plans are actually SQL Server Integration Services packages stored in msdb.

You can see the binary package by looking at msdb.dbo.sysssispackages.

You cannot easily modify the package using T-SQL, nor can you easily inspect the package contents to determine which databases are being backed up.

Instead of using Maintenance Plans, it is advisable to use SQL Server Agent in combination with some pre-designed set of scripts, such as those provided by Ola Hallengren or MinionWare

Related Question