question

LoganathanR-0963 avatar image
0 Votes"
LoganathanR-0963 asked CarrinWu-MSFT commented

Powershell script to view SQL Maintenance plan History

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

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @LoganathanR-0963, we have not get a reply from you. Did any answers could help you? If there has an answer helped, please do "Accept Answer". If not, please let us know. By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered JeffreyWilliams-3310 commented

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



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Maintenance plans do have history - from the GUI you select the maintenance plans folder, right-click the specific plan and View History. History is stored in the MSDB database - in the sysssispackages table.

Using PS - you probably could just query the tables directly to get the history.

0 Votes 0 ·
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered CarrinWu-MSFT edited

Hi @LoganathanR-0963,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.