Powershell script to view SQL Maintenance plan History

Question

Hello All,
I'm having couple of SQL Server which is configured with Maintenance plan Differential Backup, Full Backup, Log Backup & Cleanup.
I need to view the History of Maintenance plan from one system using powershell script. Any one can help me out or other kindly share the how can i get the report from all the server maintenance plan history from single machine.

Regards,
Loganathan. R

Answer 1

I need to view the History of Maintenance plan

Maintenance plans don't have a history, but the SQL Server-Agent job execution the MP do have a history.
You can use the PowerShell CmdLet dbatools => Get-DbaAgentJobHistory function to get the history.

https://www.powershellgallery.com/packages/dbatools/0.9.380/Content/functions%5CGet-DbaAgentJobHistory.ps1

Answer 2

Hi @Loganathan R ,

Welcome to Microsoft Q&A!

For SQL Server, you could use below script to get some detail about maintenance plan:

Select @@SERVERNAME [servername],  
case when D.Succeeded=1 then 'Success' when D.succeeded=0 then 'Failed' End as Result,  
A.name,B.subplan_name,D.line1,D.line2,D.line3,D.line4,  
D.line5,D.start_time,D.end_time,D.command  
From msdb.dbo.sysmaintplan_plans a inner join msdb.dbo.sysmaintplan_subplans b on a.id=b.plan_id  
inner join msdb.dbo.sysmaintplan_log c on c.plan_id=b.plan_id and c.Subplan_id=b.subplan_id  
inner join msdb.dbo.sysmaintplan_logdetail d on d.task_detail_id=c.task_detail_id  
Order By D.start_time DESC  

But I am not familiar with Powershell. After some research, I found a document as below, hope it will help you:
Getting Details from a Maintenance Plan using PowerShell

Best regards,
Carrin

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.