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 SQLMaint jobs to a central server

When setting up a maintenance plan through the wizard, there is the option to
select a remote server to receive the job history. However, this option does
not seem available when using SqlMaint to setup jobs. I have a setup where 8
remote servers are setup with one central MSX server. Unfortunately, I can't
figure out how to save the job history to the central MSX server and not to
the local servers. Anyone know how the maintenance plan wizard sets things
up to save job histories remotely?
Thanks,
Alex Gadea
I don't think that it is the job history which is written to the remote server, it is only the maint
plan history table(s). I might be wrong, of course...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Alex" <Alex@.discussions.microsoft.com> wrote in message
news:46236FC9-AB41-43D6-AE75-4715B66F6267@.microsoft.com...
> When setting up a maintenance plan through the wizard, there is the option to
> select a remote server to receive the job history. However, this option does
> not seem available when using SqlMaint to setup jobs. I have a setup where 8
> remote servers are setup with one central MSX server. Unfortunately, I can't
> figure out how to save the job history to the central MSX server and not to
> the local servers. Anyone know how the maintenance plan wizard sets things
> up to save job histories remotely?
> Thanks,
> Alex Gadea

SAve SQLMaint jobs to a central server

When setting up a maintenance plan through the wizard, there is the option t
o
select a remote server to receive the job history. However, this option doe
s
not seem available when using SqlMaint to setup jobs. I have a setup where
8
remote servers are setup with one central MSX server. Unfortunately, I can'
t
figure out how to save the job history to the central MSX server and not to
the local servers. Anyone know how the maintenance plan wizard sets things
up to save job histories remotely?
Thanks,
Alex GadeaI don't think that it is the job history which is written to the remote serv
er, it is only the maint
plan history table(s). I might be wrong, of course...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Alex" <Alex@.discussions.microsoft.com> wrote in message
news:46236FC9-AB41-43D6-AE75-4715B66F6267@.microsoft.com...
> When setting up a maintenance plan through the wizard, there is the option
to
> select a remote server to receive the job history. However, this option d
oes
> not seem available when using SqlMaint to setup jobs. I have a setup wher
e 8
> remote servers are setup with one central MSX server. Unfortunately, I ca
n't
> figure out how to save the job history to the central MSX server and not t
o
> the local servers. Anyone know how the maintenance plan wizard sets thing
s
> up to save job histories remotely?
> Thanks,
> Alex Gadea

SAve SQLMaint jobs to a central server

When setting up a maintenance plan through the wizard, there is the option to
select a remote server to receive the job history. However, this option does
not seem available when using SqlMaint to setup jobs. I have a setup where 8
remote servers are setup with one central MSX server. Unfortunately, I can't
figure out how to save the job history to the central MSX server and not to
the local servers. Anyone know how the maintenance plan wizard sets things
up to save job histories remotely?
Thanks,
Alex GadeaI don't think that it is the job history which is written to the remote server, it is only the maint
plan history table(s). I might be wrong, of course...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Alex" <Alex@.discussions.microsoft.com> wrote in message
news:46236FC9-AB41-43D6-AE75-4715B66F6267@.microsoft.com...
> When setting up a maintenance plan through the wizard, there is the option to
> select a remote server to receive the job history. However, this option does
> not seem available when using SqlMaint to setup jobs. I have a setup where 8
> remote servers are setup with one central MSX server. Unfortunately, I can't
> figure out how to save the job history to the central MSX server and not to
> the local servers. Anyone know how the maintenance plan wizard sets things
> up to save job histories remotely?
> Thanks,
> Alex Gadea

SAN: tempdb and log files not isolated from others files

HI,
SQL Server 7.0 on NT 4 (clustered)
- 3 DB totaling 7 + 2 + 1 = 10 GIG
- 100 users
- Jobs that insert many (1000 - 5000) rows in databases are executed during
office hour.
Our technical staff want to install our databases on a new Compaq SAN in
this way --> One big array containg 36 disk drives.
They told me that they can create "logical volume" for RAID 1 and RAID5 but
"tempdb" and "log files" are not going to be isolated on a small group on
disk. Data files, log files, tempdb and others files are all going to be
striped on the big array of 36 disks. In others word, we can have 1 piece of
tempdb, 1 piece on log, 1 piece of data file, 1 piece of a .bak file etc...
on the same disk.
With this scenario, is it possible to encounter bad performace ?
Thank you
dannyIt's really hard to say without actually testing it on the hardware you
mentioned and under the max load. It doesn't sound like you have that many
transactions but it really depends on how well the schema was designed, how
you actual use the data, how many and what type of transactions, how much
cache in the SAN etc, etc. 36 Drives will help to reduce head contention
and it will probably be OK but noone can say for sure without much more
intimate knowledge of the above mentioned.
--
Andrew J. Kelly
SQL Server MVP
"Danny Presse" <dpresse@.congresmtl.com> wrote in message
news:%23b3fs0hVDHA.1832@.TK2MSFTNGP09.phx.gbl...
> HI,
> SQL Server 7.0 on NT 4 (clustered)
> - 3 DB totaling 7 + 2 + 1 = 10 GIG
> - 100 users
> - Jobs that insert many (1000 - 5000) rows in databases are executed
during
> office hour.
> Our technical staff want to install our databases on a new Compaq SAN in
> this way --> One big array containg 36 disk drives.
> They told me that they can create "logical volume" for RAID 1 and RAID5
but
> "tempdb" and "log files" are not going to be isolated on a small group on
> disk. Data files, log files, tempdb and others files are all going to be
> striped on the big array of 36 disks. In others word, we can have 1 piece
of
> tempdb, 1 piece on log, 1 piece of data file, 1 piece of a .bak file
etc...
> on the same disk.
> With this scenario, is it possible to encounter bad performace ?
> Thank you
> danny
>