Question on Changing Jobs in SQL Server 2000

We have a bunch of databases on this SQL Server 2000 and for each
database we pretty much have the same kind of job running daily and
weekly. We are running out of disk space where we used to normally
backup the databases so we now have a new drive which will be used only
for backups so I need to change all my jobs to backup the databases to
the new drive. I can do that manually by going into each job to change
the drive path, but I was wondering if there was a way to update the
system table so that I did not have to manually change all my jobs.
Any help, ideas in this regard will be greatly appreciated.
ThanksHi
This is where having devices would make the task easier! But you can script
the jobs from Enterprise manager and then globally edit the file created.
John
"shub" wrote:

> We have a bunch of databases on this SQL Server 2000 and for each
> database we pretty much have the same kind of job running daily and
> weekly. We are running out of disk space where we used to normally
> backup the databases so we now have a new drive which will be used only
> for backups so I need to change all my jobs to backup the databases to
> the new drive. I can do that manually by going into each job to change
> the drive path, but I was wondering if there was a way to update the
> system table so that I did not have to manually change all my jobs.
> Any help, ideas in this regard will be greatly appreciated.
> Thanks
>|||John
Thanks for your response. Wouldnt the script drop the existing job and
create a brand new one which would cause me to lose all my job history?
Is there a way to get around that?
Thanks|||Hi
Yes it would, the jobs would have different Ids when re-created so you would
have to export everything before doing the scripting. Another reason for
using devices.
John
"shub" wrote:

> John
> Thanks for your response. Wouldnt the script drop the existing job and
> create a brand new one which would cause me to lose all my job history?
> Is there a way to get around that?
> Thanks
>|||John,
If we had devices in place would't we still need to change tha path off
all the devices that we set up for all the different databases? Also
can you set up devices if the physical location of the back up files
are in different computers in the network, basically does it support
UNC? I will look it up myself, but I would sure appreciate your input
as well.
Thanks again|||Hi
If you need to change the job to make it use a device it will not change the
problems of loosing the history, only make it more flexible in the future.
You should only be keeping a certain amount of history as this information
will bloat your msdb, if you need to retain this for a prolonged period then
you may want to put into place a system that will copy it elsewhere.
Devices can use UNC paths, there is an example of sp_adddumpdevice in books
online that shows exactly this. If you are backing up to another machine it
is worth considering a dedicated (1GB) subnet and adding extra cards to
improve performance.
John
"shub" wrote:

> John,
> If we had devices in place would't we still need to change tha path off
> all the devices that we set up for all the different databases? Also
> can you set up devices if the physical location of the back up files
> are in different computers in the network, basically does it support
> UNC? I will look it up myself, but I would sure appreciate your input
> as well.
> Thanks again
>|||I believe when all our jobs were created originally they were all
created by The maintenance plan wizard and I don't see you have an
option of backing up databases to devices.
Maybe in the near future we need to look at redoing all our jobs to use
the devices.
Thank you for all your help.