Saving and restoring Jobs

Hi! I've been scouring web sites and local information and not having any luck finding out how to save and restore jobs.
The problem comes up that we have around 10 to 15 jobs that we use for daily business that we have to rebuild every time SQL Server crashes or we move to a new machine, etc., and I've been trying to find a way to back them up.
Can anyone help me out please?
VowelsJobs are stored in the MSDB database. So if you back that up (which should
be part of your daily routine) you should be able to restore it later on.
Another way is to script the job using EM or SQL-DMO. Here is a sample
script that you can use in a scheduled job as an Active-X job step.
Dim oSS
Set oSS = CreateObject("SQLDMO.SQLServer2")
Dim lcstring
Dim lcServer
Dim oJob
Set oJob = CreateObject("SQLDMO.Job")
Dim lcFile
lcServer = "YourDBNameHere"
oSS.LoginSecure = True
oSS.Connect lcServer
lcFile = "D:\Data\DB_Scripts\ScriptJobs" & "_" & Year(Now) & Right("0" &
Month(Now), 2) & Right("0" & Day(Now), 2) & ".sql"
For Each oJob In oSS.JobServer.Jobs
lcstring = lcstring & "-- ***** " & oJob.Name & " ****** " &
vbCrLf & vbCrLf
lcstring = lcstring & oJob.Script
Next
Dim fso, txtfile
Set fso = CreateObject("Scripting.FileSystemObject")
Set txtfile = fso.CreateTextFile(lcFile, True)
txtfile.Write (lcstring)
txtfile.Close
Set oSS = Nothing
Set fso = Nothing
End Sub
Andrew J. Kelly
SQL Server MVP
"Vowels" <anonymous@.discussions.microsoft.com> wrote in message
news:358B2D03-36EE-4D64-BEEA-57784865D7DE@.microsoft.com...
> Hi! I've been scouring web sites and local information and not having any
luck finding out how to save and restore jobs.
> The problem comes up that we have around 10 to 15 jobs that we use for
daily business that we have to rebuild every time SQL Server crashes or we
move to a new machine, etc., and I've been trying to find a way to back them
up.
> Can anyone help me out please?
> Vowels

Save the return value into a variable

Hi
How can I save the returned XML Expression into a variable? I tried to this
a described on the next line, but with no luck...
I use SQL Server 2000 SP 4
DECLARE @.doc nvarchar(4000)
SET @.doc = (SELECT * FROM sysdba.Account WHERE AccountID = 'ALFKI' FOR XML
auto)
Thank's
MichelHello Michel,
Can't be done in SQL Server 2000 since the XML Serialization is always done
post query there.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||If you have SQL Server 2005, the below query is valid. And you'lll have more
features in FOR XML.
"Michel" <michel_mueller@.bluewin.ch> wrote in message
news:ujy1pp69GHA.4708@.TK2MSFTNGP05.phx.gbl...
> Hi
> How can I save the returned XML Expression into a variable? I tried to
> this a described on the next line, but with no luck...
> I use SQL Server 2000 SP 4
> DECLARE @.doc nvarchar(4000)
> SET @.doc = (SELECT * FROM sysdba.Account WHERE AccountID = 'ALFKI' FOR XML
> auto)
> Thank's
> Michel
>

Save the return value into a variable

Hi
How can I save the returned XML Expression into a variable? I tried to this
a described on the next line, but with no luck...
I use SQL Server 2000 SP 4
DECLARE @.doc nvarchar(4000)
SET @.doc = (SELECT * FROM sysdba.Account WHERE AccountID = 'ALFKI' FOR XML
auto)
Thank's
Michel
Hello Michel,
Can't be done in SQL Server 2000 since the XML Serialization is always done
post query there.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/