SQLTeam.com | Weblogs | Forums

How to query a maintenance plan


#1

Is there a way to query a maintenance plan to see if a specific database is included in it?

Thanks


#2

By "query" if you mean a T-SQL query, I don't know of a way to do that.

Maintenance plans are stored as SSIS packages in the MSDB database. You can export those packages and search in the XML. But if you have to go through the trouble of doing that, you might as well open up the maintenance plan in Object explorer and inspect it to see which databases are included.


#3

Look in the msdb db for tables beginning with:
dbo.sysdbmaintplan
In particular:
dbo.sysdbmaintplans
dbo.sysdbmaintplan_databases
Those tables should give you what you need.