none
How do you export SQL 2005 Maintenance Plans?

    Question

  • I am in SQL management studio right now and I have created a maintenance plan.  However I see no way of exporting that maintenance plan so I can install it on another SQL 2005 server.  I would normally expect to right click on my maintenace plan and have the ability to import or export, that feature is not there.  Infact I have found that there are a lot of features missing from management studio, in general it lacks a lot of the right click functionality that use to be in enterprise manager which is a shame, it saved a lot of time. 

    Is there some place to script this maintenance plan? 

    Monday, March 20, 2006 9:24 PM

Answers

All replies

  • Is this possible?!?!  Having standardized maintenance plans is a necessity for our organization as we deal with hundreds of clients and some of which are now jumping on the SQL 2005 band wagon.

    SSIS packages are not an option I don't believe because they do not run on SQL express correct?

    Friday, May 05, 2006 8:41 PM
  • I sort of found how to get part of the package xml.  But it chops off nearly all of the XML.  Is there a better query to use?

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

    Friday, May 05, 2006 9:23 PM
  • Okay, figured it out.  You can get the original package xml data (thus a .dtsx file) this way:

    select cast(cast(packagedata as varbinary(max)) as varchar(max)) from sysdtspackages90

    Regards,

    Matt Olson

    Olsen Thielen Technologies

    Friday, May 05, 2006 9:34 PM
  • There is also an easier way of doing this:

    http://forums.microsoft.com/MSDN/showpost.aspx?postid=389484&siteid=1

    Matt

    Monday, May 08, 2006 4:48 PM
  • Do you know how you can put that SSIS package back to a different server?

    I used exec msdb.dbo.sp_dts_putpackage 'System DB Maintenance Plan Ver3.11',@id,'',@createdate,@folderid,@varbinary,0,6,1,0,1,'',@verid

    to copy the maintenance plan to another server. The copy is ok, but it does not show in maintenance plan and you can not run it.

    Wednesday, May 10, 2006 3:16 PM
  • It is actually a little complicated to do it with a script but it can be done as we have created a script that creates a standardized set of maintenance plans for all of our customers on SQL 2005.  It entails recreating unique GUID's for the maintenance plan and calling several more stored procedures to get it scheduled and have it show up as a maintenance plan.  I didn't want to paste the entire script here because the XML makes the post huge.  I would suggest that you see how the GUID's change in the maintenance plan XML and compare it to the sysmaintplan_plans, sysmaintplan_subplans.  This will show you how to replace the GUID values within the @xml declaration with unique guids that were generated for @id and @subplanGUID:

    declare @name as varchar(50),
      @id as uniqueidentifier,
      @description as varchar(500),
      @date as datetime,
      @folderid as uniqueidentifier,
      @xmlVarchar as nvarchar(max),
      @xml as xml,
      @packagedata as varbinary(max),
      @verid as uniqueidentifier,
      @servername as nvarchar(100),
      @jobId BINARY(16),
      @jobGUID uniqueidentifier,
      @sqlStmt as varchar(max),
      @schedule_id int,
      @subPlanGUID as uniqueidentifier,
      @existingMaintID as nvarchar(50),
      @subPlanGUIDChar varchar(50)

    SET NOCOUNT ON

    /*Setup Weekly Database Maintenance Plan*/
    select @servername=rtrim(@@servername)
    select @id=newid()
    select @verid=newid()
    select @subPlanGUID=newid()
    select @subPlanGUIDChar=@subPlanGUID
    select @name='Weekly Database Maintenance'
    select @description='Olsen Thielen Technologies.  Weekly Database Maintenance plan.  visit http://www.ottechnologies.com for more information'
    select @date=getdate()
    select @folderid=folderid from msdb..sysdtspackagefolders90 where foldername='Maintenance Plans'
    select @xml='Put the XML of your maintenance plan here, you also MUST change a few of the unique GUIDS using the @ID and @subPlanGUID above.  Use '
    SET @xml.modify('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
      replace value of (/DTS:Executable/DTS:PackageVariable[1]/DTS:Property[3]/text())[1]
      with xs:string(sql:variable("@subPlanGUIDChar"))')

    select @packagedata=cast(@xml as varbinary(max))

    if exists(select 1 from msdb..sysjobs_view where name='Weekly Database Maintenance')
    begin
     select @existingMaintID=id from msdb..sysmaintplan_plans where name='Weekly Database Maintenance'
     EXECUTE msdb..sp_dts_deletepackage @name=N'Weekly Database Maintenance',@folderid=@folderid
     EXECUTE msdb..sp_maintplan_delete_plan @plan_id=@existingMaintID
    end

    exec msdb..sp_dts_putpackage @name,@id,@description,@date,@folderid,@packagedata,@packageformat=0,@packagetype=6,@vermajor=1,@verminor=0,@verbuild=1,@vercomments='',@verid='00000000-0000-0000-0000-000000000000'

    EXEC  msdb..sp_add_job @job_name=N'Weekly Database Maintenance', @enabled=1, @start_step_id=1, @category_name=N'Database Maintenance', @job_id = @jobId OUTPUT
    EXEC msdb..sp_add_jobserver @job_name=N'Weekly Database Maintenance', @server_name = @servername
    exec msdb..sp_sqlagent_refresh_job @job_id = @jobId

    SELECT @jobGUID=sv.job_id FROM msdb.dbo.sysjobs_view AS sv WHERE (sv.name=N'Weekly Database Maintenance')

    select @sqlStmt = '/Server ' + rtrim(@servername) + ' /SQL "Maintenance Plans\Weekly Database Maintenance" /set "\Package\Weekly Database Maintenance.Disable;false"'
    EXEC msdb..sp_add_jobstep @job_id=@jobGUID, @step_name=N'Weekly Database Maintenance', @step_id=1, @subsystem=N'SSIS', @command=@sqlStmt

    EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobGUID, @name=N'Weekly Database Maintenance-Schedule',
      @enabled=1,
      @freq_type=8,
      @freq_interval=1,
      @freq_subday_type=1,
      @freq_subday_interval=0,
      @freq_relative_interval=0,
      @freq_recurrence_factor=1,
      @active_start_date=20060508,
      @active_end_date=99991231,
      @active_start_time=0,
      @active_end_time=235959, @schedule_id = @schedule_id OUTPUT

    EXECUTE msdb..sp_maintplan_update_subplan @subPlanGUID,@id,N'Weekly Database Maintenance',N'',@jobGUID,@schedule_id,1
    GO

    Matt Olson

    Olsen Thielen Technologies

    Monday, May 15, 2006 4:23 PM
  • select @xml='Put the XML of your maintenance plan here, you also MUST change a few of the unique GUIDS using the @ID and @subPlanGUID above.  Use '
    SET @xml.modify('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
      replace value of (/DTS:Executable/DTS:PackageVariable[1]/DTS:Property[3]/text())[1]
      with xs:string(sql:variable("@subPlanGUIDChar"))') 

    Is this the same XML file that we produce from: Select Cast(Cast(packagedata as varbinary(max)) as varchar(max)) from sysdtspackages90?

    Can I use this select statement above in the Select @xml=  or create a dtsx file and call dtexec.

     

    Tuesday, September 26, 2006 5:00 PM
  • I retrieved the xml file using the Select Cast(Cast(packagedata as varbinary(max)) as varchar(max))  method.  However there may be an easier way.

    After I retrieved the xml text that defines the maintenance plan I went and modified that xml so that when we do the select @XML='Put the XML of your maintenance plan here' text that it does some of the following:

    Replaces the connectionString to the server we are actually on

    <DTS:Property DTS:Name="ConnectionString">server='''+rtrim(@servername)+''';Trusted_Connection=true;Application Name=

    Replaces the ObjectName which is a GUID with a new unique ID

    <DTS:Property DTS:Name="ObjectName">{'+rtrim(cast(@subPlanGUID as nvarchar(50)))+'}</DTS:Property>

    <DTS:Property DTS:Name="DTSID">{'+rtrim(cast(@subPlanGUID as nvarchar(50)))+'}</DTS:Property>

    This is the text that is changed at the end of the xml file

    EXECUTE msdb..sp_maintplan_open_logentry ''{'+ rtrim(cast(@id as nvarchar(50))) +'}'', ''{'+rtrim(cast(@subPlanGUID as nvarchar(50)))+'}'',NULL, @Guid OUTPUT &#xA;&#xA; Select CONVERT(nvarchar(38),@Guid) AS RunId

    So basically with those changes (I think I have them all, I may have missed a few, not sure) you get a custom DTS XML document which defines an entire set of maintenance plan steps/actions to perform and it is server agnostic as it dynamically builds a unique DTS XML document based on the servername you are running it on, and a unique set of GUID's for the @subPlanGUID and @id parameters.

    Hope that helps. 

    Matt Olson

    Olsen Thielen Technologies

    www.ottechnologies.com

    Tuesday, September 26, 2006 5:56 PM
  • Hi Matt,

    After you import a plan via this method, are you able to successfully open it in Management Studio?  I'm able to import a plan via your script, but I get an error "Value cannot be null. Parameter: name (system.design)" when I try to open the imported package.  Any ideas what I could be doing wrong?

    As a test, I also tried just importing with the unmodified XLM to rule out that I wasn't typoing something. Same results.

    Any suggestions would be greatly apreciated!!

    Thanks
    Charlie

    Friday, December 29, 2006 7:31 PM
  • Hi Charlie,

    I'm not sure what could be wrong, and yes I can go in and edit the resulting maintenance plan.

    Email me at molson AT ottechnologies.com and I can send you the one we use for our clients that are on SQL 2005...

    Note:  I have not tested this on SQL 2005 SP2 CTP, if you are using that and getting an error let me know.

    Matt

    Friday, December 29, 2006 7:46 PM
  • Can anybody tell me how to export a DTSX package to DTS (2000 style) package?

    Basic I have one SQL 2005 and I build a package for maintenance. All other server are win 2000 so, I need to import the package that i made in 2005 from 2005 to 2000.

    Any ideea?

    Thanks for help,

    Adrian

    Friday, March 02, 2007 2:26 PM
  • It is not possible.  2005 DTS packages are completely different from SQL 2000 DTS packages.  You will have to recreate the DTS package you made in SQL 2005 in SQL 2000, then you can export it from your SQL 2000 machine to all of your other SQL 2000 machines.

    P.S. Start a new thread as this isn't related to exporting SQL 2005 maintenance plans.

    Friday, March 02, 2007 2:32 PM
  • Application Name=

    Replaces the ObjectName which is a GUID with a new unique ID

    I don't understand what is to go in the the application name field.

     

    Wednesday, April 04, 2007 10:15 AM
  •  

    I have replaced all occurances of the DTSID with the below
    <DTSProperty DTS:Name="DTSID">{'+rtrim(cast(@subPlanGUID as nvarchar(50)))+'}</DTSProperty>

     

    For the object name below this seems to include different text rather GUID's so what do I need to replace the below with

    <DTSProperty DTS:Name="ObjectName">{'+rtrim(cast(@subPlanGUID as nvarchar(50)))+'}</DTSProperty>

     

    my xml contains

    <DTSProperty DTS:Name="ObjectName">Propagate</DTSProperty>

    <DTSProperty DTS:Name="ObjectName">Maintenance Cleanup Task</DTSProperty> etc

     

    I have having difficulty working this out. Any help would be great

    Wednesday, April 04, 2007 10:40 AM

  • Could you explain why this xml line is required

    SET @xml.modify('declare namespace DTS="www.microsoft.com/SqlServer/Dts";

     

    this doesn't appear in my xml output file so do I need to create it


      replace value of (/DTS: Executable/DTS: PackageVariable[1]/DTS: Property[3]/text())[1]
      with xs: string(sql:variable("@subPlanGUIDChar"))') 

    I am getting an error reading in the xml file in Management Studio.  The xml is around 38000 characters.

    Msg 9400, Level 16, State 1, Line 40  XML parsing: line 1, character 4000, unexpected end of input

     

    I have 3 extra parameter which I presume need to be replaced with {'+rtrim(cast(@subPlanGUID as nvarchar(50)))+'}


    <DTS: Property DTS:Name="ObjectName">Reporting Task for subplan-{884D7898-A647-4F74-8E43-02CD033FF142}</DTS: Property>

    <SQLTask: SqlTaskData SQLTask:Connection="{F2411554-45F9-4457-A654-3E25784C8466}"

    <DTS: Executable IDREF="{DA63B3D6-03B9-4E03-A8C0-46F7F5C7FBEA}"

    Wednesday, April 18, 2007 12:56 PM
  • I followed this step by step and get the same error as Charlie. I know this post is old but I'm really hoping someone can tell me what is wrong. I've been banging my head against a wall trying to create deployable maintenance plans for 2005. Can anyone help?!?!
    Wednesday, January 27, 2010 8:18 PM
  • Here's a little more info...

    The script runs and completes without error and the plan shows under maintenance plans however when you go to open it, you get:

    "Value cannot be null. Paramater name: component (System.Design)".

    The job shows up under SQL agent but when it's run it fails with this error:

    Executed as user: ABCDE\Service. ...35.00 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  2:48:02 PM  Error: 2010-01-27 14:48:03.21     Code: 0xC0010018     Source:       Description: Error loading value "<DTS:PackageVariable xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8">&lt;DtsEventHandler xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http:}</DTS:Property><DTS:Property DTS:Name="Descrip" from node "DTS:PackageVariable".  End Error  Could not load package "Maintenance Plans\Backups and Maintenance" because of error 0xC0010014.  Description: One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.  Source:   Started:  2:48:02 PM  Finished: 2:48:03 PM  Elapsed:  0.281 seconds.  The package could not be lo...  The step failed.

    Can anyone help??

    Thanks!

    Wednesday, January 27, 2010 8:55 PM