none
Exporting SQL Maintenance Plans

    Question

  • I have taken a look at the new maintenance plan designer and have come up with a maintenance plan I would like to be able to deploy on an enterprise level.  Anyone know of a way I can export the plan to another SQL 2005 server - without directly pulling from the msdb system tables?  I like the fact that you can script the output of most of the wizards, but the maintenance plan wizard does not appear to be one of them.

    Thanks,

    Matt
    Thursday, June 16, 2005 2:22 PM

Answers

  • You can import and export maintenance plans. Connect to
    Integration Services on the server where the maintenance
    plans are located. Go to Stored Packages -> MSDB ->
    Maintenance Plans. From there you can right click on
    Maintenance Plans and select Import. Or if you right click
    on one of your maintenance plans, you can select import or
    export.

    Thanks

    Monday, May 08, 2006 4:32 PM

All replies

  • I also really need to know how to do this.  We want standardized maintenance plans at all of our clients and creating these by hand is a pain in the a$$.  How can you do this?  I see that you can create SSIS packages to do the same thing, but can you deploy those on people who run SQL express??
    Friday, May 05, 2006 8:39 PM
  • You can import and export maintenance plans. Connect to
    Integration Services on the server where the maintenance
    plans are located. Go to Stored Packages -> MSDB ->
    Maintenance Plans. From there you can right click on
    Maintenance Plans and select Import. Or if you right click
    on one of your maintenance plans, you can select import or
    export.

    Thanks

    Monday, May 08, 2006 4:32 PM
  • Ahh I see!  I was always connecting to just SQL.  Forgot about all the other services in the drop down list.

    I also found another way:

    select name,cast(cast(packagedata as binary) as varchar(max)) from sysdtspackages90

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=389519&SiteID=1&mode=1

    But your way is much easier.

    Monday, May 08, 2006 4:51 PM
  • Very helpfull, thank you.

     

    What do you suggest if you have a maintenace plan in a named instance(not default), which you vould like to copy ?

    Friday, April 20, 2007 12:49 PM
  •  

    If you have all servers involved registered in Management studio the simplest way would be to simply copy all the tasks from the design window in Maintenance plans and the paste it into the design window on the destination server.   Then you will only have to correct the connection settings in each task, and fix the layout of the design and for that you could use Format-Auto Layout.
    Wednesday, October 17, 2007 12:05 PM
  • That may work if all you are dealing with is one organization.  Creating a maintenance plan script can be reused at many different organizations which is what I have done and is far more beneficial.  I am in consulting, and therefore having something like this to setup maintenance plans at client sites is an invaluable tool, because if I had to drag all those tasks and connect them all together and set schedules, etc is just chewing up more time and not being very efficient.
    Wednesday, October 17, 2007 4:47 PM
  • " Or if you right click on one of your maintenance plans, you can select import or export. "

    In SQL Server 2005 , I can not find the Import or Export on the Maintenance Plan


    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    Friday, May 07, 2010 5:37 AM
  • Thanks Zoyab for posting this worked perfectly....

    Tuesday, June 08, 2010 11:02 PM
  • Thank you, Zoyab.

    A little note. If you'd like to work with maintenance plan(s) that locate on non-default or named (or even remote) SQL server instance, see MSDN topic "Configuring the Integration Services Service" http://msdn.microsoft.com/en-us/library/ms137789.aspx in order to modify default settings of Integration Services.

    Best regards.

    Tuesday, September 07, 2010 9:55 AM
  • One additional note: if you have created a schedule for the plan, the schedule does NOT transfer with the plan.  Make sure you open the plan on the new server and set up a schedule for it.
    Wednesday, January 05, 2011 2:57 PM
  • Thank you!  This worked perfectly.
    Friday, June 03, 2011 8:40 PM
  • You can do a PowerShell script something like that (do not forget that you must open and schedule the tasks)

    # MAINTENANCE PLANS
    # import OK
    $debugpreference = 2
    $ImportFolder = "C:\"
    #"
    [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ManagedDTS") | Out-Null
    $application = New-Object Microsoft.SqlServer.Dts.Runtime.Application
    [string[]]$DtsxFiles = Get-ChildItem -Path $ImportFolder -Filter "*.dtsx" -EA 0 | select -ExpandProperty FullName
    if ($DtsxFiles) {
    	$CountMP = 0
    	foreach ($DtsxFile in $DtsxFiles) {
    		try {
    			$Package = $application.LoadPackage($DtsxFile, $Null)
    			$MaintenancePlanName = [System.IO.Path]::GetFileNameWithoutExtension($DtsxFile)
    			$application.SaveToSqlServerAs($Package, $Null, "Maintenance Plans\" + $MaintenancePlanName, '.', $Null, $Null)
    			$CountMP++
    		}
    		catch {
    			Write-Error "ERROR:" + $_.Exception.Message
    		}
    	}
    	Write-Debug "$CountMP Maintenance Plan(s) imported from '$ImportFolder'"
    }
    else {
    	Write-Warning "There is no Maintenance Plan"
    }
    
    
    # export OK
    $debugpreference = 2
    $ExportFolder = "C:\"
    #"
    [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ManagedDTS") | Out-Null
    $application = New-Object Microsoft.SqlServer.Dts.Runtime.Application
    $PackageInfos = $application.GetPackageInfos("Maintenance Plans", ".", $Null, $Null)
    if ($PackageInfos) {
    	$CountMP = 0
    	foreach ($PackageInfo in $PackageInfos) {
    		$PackageName = $PackageInfo.Name
    		$Package = $application.LoadFromSqlServer("\\Maintenance Plans\\" + $PackageName, ".", $Null, $Null, $Null)
    		$Package.ProtectionLevel=0
    		$file = Join-Path $ExportFolder ($PackageName + ".dtsx")
    		$application.SaveToXml($file, $Package, $Null)
    		$CountMP++
    	}
    	Write-Debug "$CountMP Maintenance Plan(s) exported to '$ExportFolder'"
    }
    else {
    	Write-Warning "There is no Maintenance Plan"
    }
    
    

    • Proposed as answer by emekm Monday, March 04, 2013 3:02 PM
    • Edited by emekm Monday, March 04, 2013 3:13 PM Added import
    Monday, March 04, 2013 3:02 PM