create a script for sql server 2005 database maintenance plan

hi,
after I manually create maintenance plan using SQL server Maintenence
Plan Wizard with SQL Server Management Studio, is there any way to
create a script for all these setting that I've done?
So that I can just modify the database name and run it on another pc,
the same setting will apply in this pc. Or is there other ways to do
it?
Thanks.Hi
AFAIK there is no way of doing this for the whole plan, but the SQL for
individual tasks can be views in the View T-SQL button when they are editted.
John
"skyloon" wrote:
> hi,
> after I manually create maintenance plan using SQL server Maintenence
> Plan Wizard with SQL Server Management Studio, is there any way to
> create a script for all these setting that I've done?
> So that I can just modify the database name and run it on another pc,
> the same setting will apply in this pc. Or is there other ways to do
> it?
> Thanks.
>|||Here's an idea:
A maint plan is an SSIS package stored in MSDB. You can use SSMS or DTEXEC.EXE to export that
package to a file. That file is XML. You can now modify that file and distribute it to the target
machines. (This is from theory, you need to experiment a bit...)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skyloon" <skyloon@.gmail.com> wrote in message
news:1163998620.769408.161720@.j44g2000cwa.googlegroups.com...
> hi,
> after I manually create maintenance plan using SQL server Maintenence
> Plan Wizard with SQL Server Management Studio, is there any way to
> create a script for all these setting that I've done?
> So that I can just modify the database name and run it on another pc,
> the same setting will apply in this pc. Or is there other ways to do
> it?
> Thanks.
>|||but i can't find the path of my Database Maintenance Plan file that
created under Database engine.
when i connect Integration service, can't find the physical file, how
can i open the plan using SSMS or DTEXEC.EXE?
Tibor Karaszi wrote:
> Here's an idea:
> A maint plan is an SSIS package stored in MSDB. You can use SSMS or DTEXEC.EXE to export that
> package to a file. That file is XML. You can now modify that file and distribute it to the target
> machines. (This is from theory, you need to experiment a bit...)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "skyloon" <skyloon@.gmail.com> wrote in message
> news:1163998620.769408.161720@.j44g2000cwa.googlegroups.com...
> > hi,
> > after I manually create maintenance plan using SQL server Maintenence
> > Plan Wizard with SQL Server Management Studio, is there any way to
> > create a script for all these setting that I've done?
> > So that I can just modify the database name and run it on another pc,
> > the same setting will apply in this pc. Or is there other ways to do
> > it?
> > Thanks.
> >|||You need to export it from msdb to a file first. Use either SSMS (connect object explorer to SSIS)
or DTUTIL.EXE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skyloon" <skyloon@.gmail.com> wrote in message
news:1164163325.802538.30390@.k70g2000cwa.googlegroups.com...
> but i can't find the path of my Database Maintenance Plan file that
> created under Database engine.
> when i connect Integration service, can't find the physical file, how
> can i open the plan using SSMS or DTEXEC.EXE?
> Tibor Karaszi wrote:
>> Here's an idea:
>> A maint plan is an SSIS package stored in MSDB. You can use SSMS or DTEXEC.EXE to export that
>> package to a file. That file is XML. You can now modify that file and distribute it to the target
>> machines. (This is from theory, you need to experiment a bit...)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "skyloon" <skyloon@.gmail.com> wrote in message
>> news:1163998620.769408.161720@.j44g2000cwa.googlegroups.com...
>> > hi,
>> > after I manually create maintenance plan using SQL server Maintenence
>> > Plan Wizard with SQL Server Management Studio, is there any way to
>> > create a script for all these setting that I've done?
>> > So that I can just modify the database name and run it on another pc,
>> > the same setting will apply in this pc. Or is there other ways to do
>> > it?
>> > Thanks.
>> >
>|||I've uploaded a picture for your reference because i can't find any
export function in SSMS. Mind to teach me step by step? Thanks.
http://img.photobucket.com/albums/v202/skyloon/1.jpg
Tibor Karaszi wrote:
> You need to export it from msdb to a file first. Use either SSMS (connect object explorer to SSIS)
> or DTUTIL.EXE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "skyloon" <skyloon@.gmail.com> wrote in message
> news:1164163325.802538.30390@.k70g2000cwa.googlegroups.com...
> > but i can't find the path of my Database Maintenance Plan file that
> > created under Database engine.
> > when i connect Integration service, can't find the physical file, how
> > can i open the plan using SSMS or DTEXEC.EXE?
> >
> > Tibor Karaszi wrote:
> >> Here's an idea:
> >>
> >> A maint plan is an SSIS package stored in MSDB. You can use SSMS or DTEXEC.EXE to export that
> >> package to a file. That file is XML. You can now modify that file and distribute it to the target
> >> machines. (This is from theory, you need to experiment a bit...)
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "skyloon" <skyloon@.gmail.com> wrote in message
> >> news:1163998620.769408.161720@.j44g2000cwa.googlegroups.com...
> >> > hi,
> >> > after I manually create maintenance plan using SQL server Maintenence
> >> > Plan Wizard with SQL Server Management Studio, is there any way to
> >> > create a script for all these setting that I've done?
> >> > So that I can just modify the database name and run it on another pc,
> >> > the same setting will apply in this pc. Or is there other ways to do
> >> > it?
> >> > Thanks.
> >> >
> >|||Hi
If you wish to save a package to a file you would use BIDS and not SSMS. You
may want to check out http://msdn2.microsoft.com/en-us/library/ms137712.aspx
and the package deployment tutorial.
John
"skyloon" wrote:
> I've uploaded a picture for your reference because i can't find any
> export function in SSMS. Mind to teach me step by step? Thanks.
> http://img.photobucket.com/albums/v202/skyloon/1.jpg
>
> Tibor Karaszi wrote:
> > You need to export it from msdb to a file first. Use either SSMS (connect object explorer to SSIS)
> > or DTUTIL.EXE.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "skyloon" <skyloon@.gmail.com> wrote in message
> > news:1164163325.802538.30390@.k70g2000cwa.googlegroups.com...
> > > but i can't find the path of my Database Maintenance Plan file that
> > > created under Database engine.
> > > when i connect Integration service, can't find the physical file, how
> > > can i open the plan using SSMS or DTEXEC.EXE?
> > >
> > > Tibor Karaszi wrote:
> > >> Here's an idea:
> > >>
> > >> A maint plan is an SSIS package stored in MSDB. You can use SSMS or DTEXEC.EXE to export that
> > >> package to a file. That file is XML. You can now modify that file and distribute it to the target
> > >> machines. (This is from theory, you need to experiment a bit...)
> > >>
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> http://www.karaszi.com/sqlserver/default.asp
> > >> http://www.solidqualitylearning.com/
> > >>
> > >>
> > >> "skyloon" <skyloon@.gmail.com> wrote in message
> > >> news:1163998620.769408.161720@.j44g2000cwa.googlegroups.com...
> > >> > hi,
> > >> > after I manually create maintenance plan using SQL server Maintenence
> > >> > Plan Wizard with SQL Server Management Studio, is there any way to
> > >> > create a script for all these setting that I've done?
> > >> > So that I can just modify the database name and run it on another pc,
> > >> > the same setting will apply in this pc. Or is there other ways to do
> > >> > it?
> > >> > Thanks.
> > >> >
> > >
>|||You connected Object Explorer to a SQL Server instance. You need to connect Object Explorer to SSIS.
In Object Explorer, select the Connect button (top of Object Explorer), select Integration Services,
specify the server name (no instance name, you can only have one SSIS service). Now, expand the
Stored Packages folder, MSDB, Maintenance Plans. Right-click your package and select Export.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skyloon" <skyloon@.gmail.com> wrote in message
news:1164243388.835755.301510@.j44g2000cwa.googlegroups.com...
> I've uploaded a picture for your reference because i can't find any
> export function in SSMS. Mind to teach me step by step? Thanks.
> http://img.photobucket.com/albums/v202/skyloon/1.jpg
>
> Tibor Karaszi wrote:
>> You need to export it from msdb to a file first. Use either SSMS (connect object explorer to
>> SSIS)
>> or DTUTIL.EXE.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "skyloon" <skyloon@.gmail.com> wrote in message
>> news:1164163325.802538.30390@.k70g2000cwa.googlegroups.com...
>> > but i can't find the path of my Database Maintenance Plan file that
>> > created under Database engine.
>> > when i connect Integration service, can't find the physical file, how
>> > can i open the plan using SSMS or DTEXEC.EXE?
>> >
>> > Tibor Karaszi wrote:
>> >> Here's an idea:
>> >>
>> >> A maint plan is an SSIS package stored in MSDB. You can use SSMS or DTEXEC.EXE to export that
>> >> package to a file. That file is XML. You can now modify that file and distribute it to the
>> >> target
>> >> machines. (This is from theory, you need to experiment a bit...)
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "skyloon" <skyloon@.gmail.com> wrote in message
>> >> news:1163998620.769408.161720@.j44g2000cwa.googlegroups.com...
>> >> > hi,
>> >> > after I manually create maintenance plan using SQL server Maintenence
>> >> > Plan Wizard with SQL Server Management Studio, is there any way to
>> >> > create a script for all these setting that I've done?
>> >> > So that I can just modify the database name and run it on another pc,
>> >> > the same setting will apply in this pc. Or is there other ways to do
>> >> > it?
>> >> > Thanks.
>> >> >
>> >
>