script all MSDB jobs in TSQL

I know of a way to script all SQL Agent jobs using EM. But i want to script
all my jobs on a daily basis and copy that script file to another server . I
know how i can do the daily part of it which would be scheduling it again
but whats the TSQL or command I need to run within the job step .. Thanks
You can use Distributed Management Objects (DMO), not T-SQL. Check the
article at
http://www.winnetmag.com/SQLServer/A...90/38790.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
> I know of a way to script all SQL Agent jobs using EM. But i want to
script
> all my jobs on a daily basis and copy that script file to another server .
I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
|||You can set up a job that uses an ActiveX script type job step containing
the following
Set oSQL = CreateObject("SQLDMO.SQLServer")
oSQL.Name = "(local)"
oSQL.LoginSecure = True
oSQL.LoginTimeout = 10
oSQL.Connect
Set oJobs = oSQL.JobServer.Jobs
oJobs.Script 4,"c:\jobs.sql",2048
oSQL.DisConnect
Set oSQL = Nothing
This will script all your jobs to c:\jobs.sql. For a named instance, change
the oSQL.Name
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
>I know of a way to script all SQL Agent jobs using EM. But i want to script
> all my jobs on a daily basis and copy that script file to another server .
> I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
|||one thing to watch out for is that the job owner (@.owner_login_name) will be
scripted out too. If login doesn't exist or in a different domain / env this
may cause you problems.
Could parse the Scripted text (can use the Script object just to return text
as opposed to file) and do a replace operation on the @.owner_login_name =
'xx' and replace with @.owner_login_name = null, so that sp_add_job will
create the job with owner of the user executing the script.
Then write the corrected text to File.
cheers,
Andy
"Hassan" wrote:

> I know of a way to script all SQL Agent jobs using EM. But i want to script
> all my jobs on a daily basis and copy that script file to another server . I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
>
|||try attached
rename to .vbs
usage CSCRIPT ScriptAllJobs.vbs <Server> <FileName> <owner>
Server is Source Server where you want to script jobs from
FileName is output file for the script
owner is the new owner of the jobs. either do NULL (which will give make the
owner the user who runs the create script) or 'somelogin' ie the NT Login
that should be the owner ie 'Domainx\Jobowner' - with the quotes.
hope that makes sense
I had to do a bit of hacking cos a) the owner is hard coded and b) the
servername is hard coded (ie replace this with @.@.ServerName). Not too happy
about this, I think I might have missed a DMO Option somewhere
ai
Andy.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
> I know of a way to script all SQL Agent jobs using EM. But i want to
script
> all my jobs on a daily basis and copy that script file to another server .
I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
begin 666 ScriptAllJobs.txt
M)R!38W)I<'1!;&Q*;V)S+G-Q;" -"B<@.4V-R:7!T<R!A;&P@.:F]B<R!O;B!A
M(&=I=F5N(%-13"!397)V97(@.86YD(')E<&QA8V5S('1H92!H87)D8V]D960@.
M<F5F97)E;F-E<R!T;R!A;&QO=R!T;R!B92 -"B<@.8V]P:65D('1O(&%N;W1H
M97(@.<V5R=F5R#0HG($%N9'D@.0F%L;" R-"\X+S(P,#0)=C$N, E"87-E(%9E
M<G-I;VX-"@.T*3W!T:6]N($5X<&QI8VET#0H)( T*)R!344PM1$U/(&-O;G-T
M86YT<PT*0V]N<W0@.4U%,1$U/4V-R:7!T7T1R;W!S(#T@.,0T*#0I#;VYS="!3
M44Q$34]38W)I<'1?4')I;6%R>4]B:F5C=" ](#0@.#0I#;VYS="!344Q$34]3
M8W)I<'1?5&]&:6QE3VYL>2 ](#8T#0I#;VYS="!344Q$34]38W)I<'1?07!P
M96YD=&]&:6QE(#T@.,C4V#0H-"D-O;G-T(%-13$1-3U-C<FEP=#)?3&]G:6Y3
M240@./2 X,3DR#0H-"E!U8FQI8R!38W)I<'1/8FIE8W1/<'1I;VYS#0I38W)I
M<'1/8FIE8W1/<'1I;VYS(#T@.("!344Q$34]38W)I<'1?4')I;6%R>4]B:F5C
M=" K(%\-"@.D)"5-13$1-3U-C<FEP=%]$<F]P<R -"@.T*1&EM(&]!<F=S(" @.
M(" @.(" @.(" @.(" @.(" @.(" @.(" ))R!!<F=U;65N=',-"D1I;2!7<VA3:&5L
M; D)"0DG(&9O<B!7:6YD;W=S(%-C<FEP=&EN9R!(;W-T#0I$:6T@.4V5R=F5R
M3F%M92 @.(" @.(" @.(" @.(" @.(" @.(" @.( DG('1H92!397)V97(-"D1I;2!&
M:6QE;F%M90D)"0DG($9I;&5N86UE('1O('=R:71E('1O( T*1&EM(%-13%-T
M<FEN9PT*1&EM($YE=T]W;F5R3F%M92 -"@.T*)R!#<F5A=&4@.4VAE;&P@.;V)J
M96-T#0IS970@.5W-H4VAE;&P@./2!#<F5A=&5/8FIE8W0@.*")7<V-R:7!T+E-H
M96QL(BD-"@.T*)R!087)A;65T97)S#0IS970@.;T%R9W,@./2!7<V-R:7!T+D%R
M9W5M96YT<PT*#0HG(&-H96-K(&-O<G)E8W0@.;G5M8F5R(&]F(&%R9W,-"FEF
M("AO07)G<RY#;W5N=" \/B S*2!4:&5N#0H)5W-C<FEP="Y%8VAO(")5<V%G
M92 Z(&-S8W)I<'0@.4V-R:7!T06QL2F]B<RYV8G,@./'-E<G9E<CX@./$9I;&5.
M86UE/B \3F5W3W=N97(^(@.T*"5=S8W)I<'0N16-H;R B:2YE+B @..B!C<V-R
M:7!T(%-C<FEP=$%L;$IO8G,N=F)S(%!23T1397)V97(@.2F]B<RYS<6P@.<V$B
M#0H)5W-C<FEP="Y1=6ET(" @.( T*16QS90T*"2<@.;VL@.<V\@.87-S:6=N('!A
M<F%M<PT*"5-E<G9E<DYA;64@.(" @.(" ](&]!<F=S*# I#0H)1FEL94YA;64)
M/2!O07)G<R@.Q*0T*"4YE=T]W;F5R3F%M90D](&]!<F=S*#(I#0I%;F0@.268-
M"@.T*)R!-86EN(%-C<FEP= T*4V-R:7!T06QL2F]B<R!397)V97).86UE+"!&
M:6QE3F%M90T*#0HG($5N9"!O9B!-86EN#0H-"E-U8B!38W)I<'1!;&Q*;V)S
M("A397)V97).86UE+"!&:6QE3F%M92D-"@.E$:6T@.;T1A=&%B87-E(" @.(" @.
M(" @.(" @.(" @.(" @.("<@.=&AE('1A<F=E="!D871A8F%S92!T;R!U<V4-"@.E$
M:6T@.4V-R:7!T4W1R:6YG(" @.(" @.(" @.(" @.(" @.("<@.<F5T=7)N<R!I;G1O
M(&AE<F4@.=')A;G-A8W0@.<W%L(&9O<B!O8FIE8W0-"@.E$:6T@.4U%,3W5T<'5T
M1FEL90D))R!F:6QE('1H92!S=&]R960@.<')O8V5D=7)E(&ES('=R:71T96X@.
M=&\@.#0H)1&EM(&]397)V97(-"@.E$:6T@.9G,-"@.T*"41I;2!F<T]U='!U=$9I
M;&4-"@.T*"41I;2!*;V(-"@.E$:6T@.2F]B4W1R:6YG#0H)1&EM($1R;W!%>&ES
M=&EN9TIO8E-T<FEN9PT*#0H))R!C<F5A=&4@.4U%,(%-E<G9E<B!O8FIE8W0-
M"@.E3970@.;U-E<G9E<B ]($-R96%T94]B:F5C="@.B4U%,1$U/+E-13%-E<G9E
M<B(I#0H-"@.DG(&%L=V%Y<R!U<V4@.82!T<G5S=&5D(&-O;FYE8W1I;VX@.86YD
M(&-O;FYE8W0-"@.EO4V5R=F5R+DQO9VEN4V5C=7)E(#T@.5')U90T*"6]397)V
M97(N0V]N;F5C="!397)V97).86UE("<@.=')U<W1E9"!C;VYN96-T:6]N#0H-
M"@.DG(%)O;&P@.=&AR;W5G:"!E86-H(&IO8B!A;F0@.9V5N97)A=&4@.=&AE(%-1
M3"P@.:&%C:R!O=70@.:&%R9&-O9&5D('-T=69F(&%N9"!A<'!E;F0@.=&\@.4V-R
M:7!T4W1R:6YG('9A<FEA8FQE#0H)1F]R($5A8V@.@.2F]B(&EN(&]397)V97(N
M2F]B4V5R=F5R+DIO8G,-"@.D))R!$<F]P('1H92!J;V(@.:68@.:70@.97AI<W1S
M+B!3:&]U;&0@.8F4@.82!$34\@.;W!T:6]N(&9O<B!T:&ES(#\_/S\_#0H)"41R
M;W!%>&ES=&EN9TIO8E-T<FEN9R ](")"14=)3B!44D%.4T%#5$E/3B(@.*R!V
M8D-23$8-"@.D)1')O<$5X:7-T:6YG2F]B4W1R:6YG(#T@.1')O<$5X:7-T:6YG
M2F]B4W1R:6YG("L@.(DE&($5825-44R H4T5,14-4("H@.1E)/32!M<V1B+F1B
M;RYS>7-J;V)S(%=(15)%(&YA;64@./2 G(B K($IO8BY.86UE("L@.(B<I(B K
M('9B0U),1@.T*"0E$<F]P17AI<W1I;F=*;V)3=')I;F<@./2!$<F]P17AI<W1I
M;F=*;V)3=')I;F<@.*R!V8E1!0B K(")%6$5#(&US9&(N9&)O+G-P7V1E;&5T
M95]J;V(@.0&IO8E]N86UE(#T@.)R(@.*R!*;V(N3F%M92 K("(G(B K('9B0U),
M1@.T*"0E$<F]P17AI<W1I;F=*;V)3=')I;F<@./2!$<F]P17AI<W1I;F=*;V)3
M=')I;F<@.*R B1T\B("L@.=F)#4DQ�H)"4IO8E-T<FEN9R ]($IO8BY38W)I
M<'0@.*%-13$1-3U-C<FEP=%]0<FEM87)Y3V)J96-T*0T*"0E*;V)3=')I;F<@.
M/2!215!,04-%*$IO8E-T<FEN9RP@.(F)E9VEN('1R86YS86-T:6]N(BP@.1')O
M<$5X:7-T:6YG2F]B4W1R:6YG*0T*"0D-"@.D))R!R97!L86-E('1H92!O=VYE
M<B!W:71H($Y53$P@.<V\@.=&AA="!I="!C<F5A=&5S(&IO8B!A< R!T:&4@.=7-E
M<B!E>&5C=71I;F<@.=&AE('-C<FEP= T*"0E*;V)3=')I;F<@./2!297!L86-E
M2&%R9$-O9&5D4F5F*$IO8E-T<FEN9RP@.(D!O=VYE<E]L;V=I;E]N86UE(BP@.
M3F5W3W=N97).86UE("D-"@.D))R!297!L86-E(%-E<G9E<FYA;64@.=7-E9"!I
M;B!S<%]!9&1?:F]B7U-E<G9E<B!T;R!B92!T:&4@.8W5R<F5N="!S97)V97(L
M(&]T:&5R=VES92!I="!W:6QL('5S92!T:&4@.<V5R=F5R;F%M92!S8 W)I<'0@.
M9G)O;2!I9B!L;V-A;"!I;G-T86YC90T*"0E*;V)3=')I;F<@./2!297!L86-E
M2&%R9$-O9&5D4F5F*$IO8E-T<FEN9RP@.(D!S97)V97)?;F%M92(L(") 0%-%
M4E9%4DY!344B*0T*#0H)"2<@.061D('1O('1H92!E>&ES=&EN9 R!3=')I;F<-
M"@.D)4V-R:7!T4W1R:6YG(#T@.4V-R:7!T4W1R:6YG("L@.=F)#4DQ&("L@.2F]B
M4W1R:6YG#0H)"5=38W)I<'0N16-H;R B5W)I=&EN9R!J;V(@.(B K($IO8BY.
M86UE("L@.(B!T;R B("L@.1FEL94YA;64-"@.E.97AT( T*#0H))R!.;W<@.9FEN
M86QL>2!W<FET92!T:&4@.9FEL90T*"7-E="!F<R ]($-R96%T94]B:F5C="@.B
M4V-R:7!T:6YG+D9I;&53>7-T96U/8FIE8W0B*0T*"5-E="!F<T]U='!U=$9I
M;&4@./2!F<RY#<F5A=&5497AT1FEL92A&:6QE3F%M92P@.5')U92D-"@.EF<T]U
M='!U=$9I;&4N5W)I=&5,:6YE(%-C<FEP=%-T<FEN9PT*"69S3W5T<'5T1FEL
M92Y#;&]S90T*16YD(%-U8@.T*#0HG(%-E87)C:"!F;W(@.82!3=')I;F<@.=&AA
M="!H87,@.0'-E<G9E<B ](&XG9&]O9F5R)R!T>7!E('-Y;G1A>"!A;F0@.<F5T
M=7)N(&XG9&]O9F5R)R L('=H97)E(%-E87)C:%-T<FEN9R!I<R! <V5R=F5R
M(&EN('1H:7,@.8V%S92X-"B<@.4V\@.=V4@.8V%N(')E<&QA8V4@.:&%R9&-O9&5D
M(')E9F5R;F-E<RX@.#0I&=6YC=&EO;B!297!L86-E2&%R9$-O9&5D4F5F("A3
M44Q3=')I;F<L(%-E87)C:%-T<FEN9RP@.4F5P;&%C95-T<FEN9RD-"@.E$:6T@.
M3F5W4W1R:6YG#0H)1&EM(%-E87)C:%-T<FEN9U!O<PT*"41I;2!396-O;F11
M=6]T95!O<PT*#0H)1&EM($QO9VEN3F%M90T*"41I;2!344Q3=')I; F=,96X-
M"@.T*"5-13%-T<FEN9TQE;B ]($QE;BA344Q3=')I;F<I#0H-"@.DG(&=E="!T
M:&4@.;&]C871I;VX@.;V8@.=&AE(&]W;F5R7VQO9VEN7VYA;64@.8VAA<@.T*"5-E
M87)C:%-T<FEN9U!O<R ]($EN4W1R*#$L(%-13%-T<FEN9RP@.4V5A<F-H4W1R
M:6YG+" P*0T*#0H))R!G970@.82!3=')I;F<@.+"!S=&%R=&EN9R!W:71H( $XG
M;&]G:6X@.;F%M92<-"@.E,;V=I;DYA;64@./2!-:60H4U%,4W1R:6YG+"!396%R
M8VA3=')I;F=0;W,@.*R!,14XH4V5A<F-H4W1R:6YG*2 K(#,L(%-13%-T<FEN
M9TQE;B M(%-E87)C:%-T<FEN9U!O<RD-"@.D-"@.E396-O;F11=6]T95!O<R ]
M($EN4W1R*#,L($QO9VEN3F%M92P@.(B<B+" P*0D)#0H)3&]G:6Y.86UE(#T@.
M36ED*$QO9VEN3F%M92P@.,2P@.4V5C;VYD475O=&50;W,I#0H)# 0H))R!74V-R
M:7!T+D5C:&\@.3&]G:6Y.86UE#0H)4F5P;&%C94AA<F1#;V1E9%)E9B ](%)E
M<&QA8V4H4U%,4W1R:6YG+"!,;V=I;DYA;64L(%)E<&QA8V53= ')I;F<I#0I%
6;F0@.1G5N8W1I;VX@.#0H-"@.T*#0H-"@.``
`
end
|||... or use the DTS Transfer Jobs task !
"Hassan" wrote:

> I know of a way to script all SQL Agent jobs using EM. But i want to script
> all my jobs on a daily basis and copy that script file to another server . I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
>