Getting Details from a Maintenance Plan using PowerShell

Words: 400

Time to read: ~ 2 minutes

Maintenance Plans.

I’m not very fond of them, especially for taking care of my backups. Then again I’m neither omnipotent or a time-traveler so I can’t go back in time and stop people from using them.

This doesn’t stop me from moving my backups off them to another solution such as Ola Hallengren scripts for example. Before I can do that though, I have to know what the maintenance plans are doing.

No way am I going to go to the Maintenance Plan that I find and click Modify…then Edit…then one tab, then the other, and the other. Not to mention checking and recording all the different databases that can be selected. Finally repeating that for every single Maintenance Plan that I have. 

That’s just not a viable solution for me. That’s a worst case scenario!

Getting the Information

It’s surprisingly difficult to get this information in SQL Server. In fact I was quite stuck trying to figure out how to get this information when I realized that the good people over at Brent Ozar Unlimited already do some checking on this for their sp_Blitz tool.

A quick look at the above code showed me that dbo.sysssispackages was what I was looking for. Combine this with:

• 1. Some hack-y SQL for the frequency in human readable format, and
• 2. Some even more hack-y SQL to join on the SQL Agent Job name

And we had pretty much the XML for the Maintenance Plan and the SQL Agent Job Schedule that they were against.

PowerShell-ing it

All that was left to do was to scour the XML for all the details that I needed. Granted I could have used SQL for this but I went with PowerShell. 

“Why?” you ask. Oh a couple of reasons…
I may want to run this against multiple servers eventually, or 
I’m going to be using this as part of another PowerShell process in the works, or
I really enjoy using PowerShell and dbatools, but mainly
because I can.

The PowerShell Code

I decided to throw the code up on Github. This way I can add tests and people can pull, fix, and generally improve the code to their hearts content.
The main script is here though:

https://github.com/shaneis/MaintenancePlans/blob/master/Get-MaintenancePlanDetails.ps1

Quick Test Drive

Get-MaintenancePlanDetail

Let me know what you think!