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.
>
|||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:[vbcol=seagreen]
> 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.googlegr oups.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:[vbcol=seagreen]
> 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.googlegro ups.com...
|||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:
>
Showing posts with label maintenance. Show all posts
Showing posts with label maintenance. Show all posts
Sunday, March 25, 2012
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.E
XE to export that
package to a file. That file is XML. You can now modify that file and distri
bute 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:[vbcol=seagreen]
> 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 dist
ribute 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...|||You need to export it from msdb to a file first. Use either SSMS (connect ob
ject 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:
>|||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:[vbcol=seagreen]
> 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...|||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 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), sele
ct Integration Services,
specify the server name (no instance name, you can only have one SSIS servic
e). Now, expand the
Stored Packages folder, MSDB, Maintenance Plans. Right-click your package an
d 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:
>
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.E
XE to export that
package to a file. That file is XML. You can now modify that file and distri
bute 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:[vbcol=seagreen]
> 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 dist
ribute 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...|||You need to export it from msdb to a file first. Use either SSMS (connect ob
ject 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:
>|||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:[vbcol=seagreen]
> 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...|||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 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), sele
ct Integration Services,
specify the server name (no instance name, you can only have one SSIS servic
e). Now, expand the
Stored Packages folder, MSDB, Maintenance Plans. Right-click your package an
d 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:
>
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.
>> >> >
>> >
>
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.
>> >> >
>> >
>
Create a new maintenance plan
I have installed SQLServer 2005 from Small Business Server premium edition. The package has been upgraded to SP2 and all the fixes have been applied.
I want to create a new maintenance plan to automate my DB backup procedures using SQL Server Management Studio. I start Management Studio and right click on the management folder and I I see is "Refresh" there is no "Create New Maintenance" plan presented.
I have tried this using the sa built-in account and the server administrator account both of which have sysadmin priviledges. I have also tried it from a client machine with another sysadmin userid but they all give the same results.
I checked to see if SSIS was installed. The add/remove programs wizard for SQL Server shows Integration Services SP2 is installed but I went ahead anyway and re-installed without error.
It seems that either SSIS is not running or I my user roles are preventing the maintenance wizard from running. Is there some way that I can tell if, in fact, SSIS is properly installed and running?
Thanks.
To create or manage maintenance plans, you must be a member of the sysadmin fixed server role. Note that Object Explorer only displays maintenance plans if the user is a member of the sysadmin fixed server role.
See http://technet.microsoft.com/en-us/library/ms189036(SQL.90).aspxsqlCreate a new maintenance plan
I have installed SQLServer 2005 from Small Business Server premium edition. The package has been upgraded to SP2 and all the fixes have been applied.
I want to create a new maintenance plan to automate my DB backup procedures using SQL Server Management Studio. I start Management Studio and right click on the management folder and I I see is "Refresh" there is no "Create New Maintenance" plan presented.
I have tried this using the sa built-in account and the server administrator account both of which have sysadmin priviledges. I have also tried it from a client machine with another sysadmin userid but they all give the same results.
I checked to see if SSIS was installed. The add/remove programs wizard for SQL Server shows Integration Services SP2 is installed but I went ahead anyway and re-installed without error.
It seems that either SSIS is not running or I my user roles are preventing the maintenance wizard from running. Is there some way that I can tell if, in fact, SSIS is properly installed and running?
Thanks.
To create or manage maintenance plans, you must be a member of the sysadmin fixed server role. Note that Object Explorer only displays maintenance plans if the user is a member of the sysadmin fixed server role.
See http://technet.microsoft.com/en-us/library/ms189036(SQL.90).aspx
Subscribe to:
Posts (Atom)