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:

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.

  • Have you tried: sysdbmaintplan_databases? – McNets Jan 4 '17 at 13:52
  • @McNets. Nope. But now that I have, the resultset is empty. Also. I looked through the msdb database and didn't see that view. In fact I couldn't see the sysmaintplan_subplans view either. Do you know why this would be the case? – Zach Smith Jan 4 '17 at 14:28
  • You can use select * FROM sys.databases but I'm not sure if it is deprecated. msdn.microsoft.com/en-us/library/ms190275.aspx – McNets Jan 4 '17 at 14:39

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

  • 1
    I can't agree more. Why reinvent the wheel? – SQL_Underworld Jan 4 '17 at 16:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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