Can I copy the script from the Execute T-SQL statement Task in a maintenance plan (MP)

Brass Contributor

I want to create a backup of all our MP 

 

SGeorgie_0-1671643889574.png

 

 

SGeorgie_2-1671643949867.png

 

I want to make a database of the scripts in each MP

 

Can I do this easily

 

I only have Database Engine not Integration Access in SQL

 

Thanks

 

3 Replies
Can you elaborate more it's kind of unclear requirement.

If you wanted to take backup of all databases, you can achieve it via Maintenance plan.
If you want to execute the script via maintenance plan you can achieve the same.

@HarshKumar994     I am not sure how else to put it.

 

I want to make an excel/word doc of all our maintenance plans (script/view/frequency etc etc) rather than go in one-by-one and copying the info,, is there an easy way to extract multiple maintenance plans at once?

 

Thanks

* When you create a maintenance plan, a job is automatically created in SQL Server Agent under Jobs. So now go to SQL Server Agent, expand jobs, and look for your job. Right click on it and select Script job as... Drop and recreate to... New query editor window.

You can use the following options to return a list of SQL Server Agent jobs with T-SQL:
Option 1: Execute the sp_help_job stored procedure.
Option 2: Query the sysjobs_view view.
Option 3: Query the sysjobs table directly.

* ==========Powershel Script to Export all SQL Agent Jobs ===============
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null
$serverInstance = "InstanceName"

$server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $serverInstance

$jobs = $server.JobServer.Jobs
#$jobs = $server.JobServer.Jobs | where-object {$_.category -eq "[your category]"}

if ($jobs -ne $null)
{


ForEach ( $job in $jobs )
{
$FileName = "D:\HarshKumar\SQLServerAgentJobs\" + $job.Name + ".sql"
$job.Script() | Out-File -filepath $FileName
}
}
==============================

* You can export the maintenance plans as well. You need to connect to Integration Services of the instance. Expand the stored packages then MSDB then expand Maintenance plans. Right click on MP and select a export package. Then you can move the package to another SQL Server.

As per my knowledge we can't directly script out Maintenance Plans in SQL Server.