"victoria" <victoria@discussions.microsoft.com> wrote in message
news:4D6EC150-CB2A-479A-B882-99934D0A21E7@microsoft.com...
>I have been looking to see if anyone has written a script to create the
> maintenance plans in SQL 2005. The previous commands like
> sp_add_maintenance_plan have all been depreciated. It appears Microsoft
> don't
> want you to script the creation. By having it scripted makes it easier to
> ensure the plans are set up the same on all servers and less time
> consuming
> to install.
>
> I tried the option of exporting and import however this doesn't work in so
> far as it doesn't appear to keep the local connection or the databases
> selected in the plan e.g., msdb or all user databases. It would be much
> quicker to be able to run this as part of the auto install.
>
> It appears to use this dll Microsoft.SqlServer.MaintenancePlanTasks.dll
> and
> other scripts I have seen runing the backend are
> sp_dts_getfolder
> sp_dts_addfolder
> sp_dts_deletefolder
> sp_dts_renamefolder
> sp_dts_listfolders
> sp_dts_getpackage
> sp_dts_putpackage
> sp_dts_deletepackage
> sp_dts_listpackages
> sp_dts_getpackageroles
> sp_dts_setpackageroles
>
> Does anyone have ideas on how to script out the maintenance plan creation?
>

> Just creating one on the server then exporting it to
> another is not very useful as I have found that all the core content is
> lost
> and only the blank tasks are moved.

"narayaana" wrote:

> maintenance plans are now SSIS (former DTS) packages,
> open SSIS, this tool will enable you to create packages that you can then
> deploy to any server.
>
> "victoria" <victoria@discussions.microsoft.com> wrote in message
> news:4D6EC150-CB2A-479A-B882-99934D0A21E7@microsoft.com...
> >I have been looking to see if anyone has written a script to create the
> > maintenance plans in SQL 2005. The previous commands like
> > sp_add_maintenance_plan have all been depreciated. It appears Microsoft
> > don't
> > want you to script the creation. By having it scripted makes it easier to
> > ensure the plans are set up the same on all servers and less time
> > consuming
> > to install.
> >
> > I tried the option of exporting and import however this doesn't work in so
> > far as it doesn't appear to keep the local connection or the databases
> > selected in the plan e.g., msdb or all user databases. It would be much
> > quicker to be able to run this as part of the auto install.
> >
> > It appears to use this dll Microsoft.SqlServer.MaintenancePlanTasks.dll
> > and
> > other scripts I have seen runing the backend are
> > sp_dts_getfolder
> > sp_dts_addfolder
> > sp_dts_deletefolder
> > sp_dts_renamefolder
> > sp_dts_listfolders
> > sp_dts_getpackage
> > sp_dts_putpackage
> > sp_dts_deletepackage
> > sp_dts_listpackages
> > sp_dts_getpackageroles
> > sp_dts_setpackageroles
> >
> > Does anyone have ideas on how to script out the maintenance plan creation?
> >
>
>