Where is Maintenance Plan data stored?

• paul.bankston

  SSC Veteran

  Points: 231

  More actions

  January 24, 2012 at 10:29 am

  #267195

  Is there a database\table where the data in a maintenance plan is stored? For example, I need to get list of databases that are scheduled to be backed up in a maintenance plan. It would be nice to know where Job data is stored also.

  Thanks

• GSquared

  SSC Guru

  Points: 260824

  More actions

  January 24, 2012 at 11:40 am

  #1437861

  All of that is stored in msdb. Maintenance plans are stored as SSIS packages, which aren't particularly query-friendly in the way you're asking. They're stored in msdb.dbo.sysssispackages, but they're stored as a varbinary blob, not as XML or anything you can readily query.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• GSquared

  SSC Guru

  Points: 260824

  More actions

  January 24, 2012 at 11:42 am

  #1437863

  I looked a little. See if this article helps: http://www.mssqltips.com/sqlservertip/1137/supporting-tables-for-sql-server-2005-style-maintenance-plans/

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• arun7787

  Old Hand

  Points: 300

  More actions

  January 25, 2012 at 5:19 am

  #1438175

  Thanks