Getting Maintenance Plan Information From Powershell

Shane O’Neill gives us the low-down on what we need to do in order to retrieve maintenance plan information from SQL Server using Powershell:

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.

Shane has made the code available as well, so check it out if you have any maintenance plans you’re trying to understand and maybe even get away from.

Related Posts

Backing Up Database to Azure Blob Storage

Jamie Wick shows us how we can back up database directly to Azure Blob Storage: Azure storage, as a backup destination for SQL backups, is a great option for organizations that are contemplating replacing older on-prem NAS appliances or improve their Disaster Recovery functionality. The tiered storage pricing, along with local and global redundancy options, […]

Read More

Quick Thoughts on Dot-Sourcing in Powershell Modules

Cody Konior wants to speed up module loading: One of the more heated ideas is that you should combine all of your scripts into a massive file and execute it once instead. Currently dbatools uses a mix of these techniques in great detail and to great success. I don’t want to use a single file because I […]

Read More

Categories

December 2018
MTWTFSS
« Nov Jan »
 12
3456789
10111213141516
17181920212223
24252627282930
31