Forum Discussion

SGeorgie's avatar
SGeorgie
Brass Contributor
Dec 21, 2022

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

I want to create a backup of all our MP 

 

 

 

 

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

 

  • HarshKumar994's avatar
    HarshKumar994
    Brass Contributor
    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.

    • SGeorgie's avatar
      SGeorgie
      Brass Contributor

      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

      • HarshKumar994's avatar
        HarshKumar994
        Brass Contributor
        * 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.