Change UNC of DB Backup?

What is the easiest way to change the location where I backup my databases?
Can I just modify the command field of the sysjobsteps table or is there a
stored proc I can use instead? They're setup using the DB Maint plan at the
moment and I have about 20 to change on 3 servers, so I'd prefer to not do
it manually. This will be on 2 2k servers and one sql7 server.
TIA
Matt
>
> Take a look into the system stored procedure sp_update_jobstep in MSDB
> database.
>
Hari-
I've been reading about the sp_update_jobstep proc in BOL but I'm not sure
how can I take what is already there and change the command field to reflect
the new UNC. The command line for that is huge and there are 2 places that
the UNC needs to be changed in it. Can I run a select statement and exec
sp_update_jobstep on each row returned from the select using the Replace()
function?
|||One thing to note is that backing up to a UNC share has permission
implications - you may need to change the credentials of the SQL Server
and Agent services to a domain one.
Regards
Stuart
Matt Williamson wrote:
> Hari-
> I've been reading about the sp_update_jobstep proc in BOL but I'm not sure
> how can I take what is already there and change the command field to reflect
> the new UNC. The command line for that is huge and there are 2 places that
> the UNC needs to be changed in it. Can I run a select statement and exec
> sp_update_jobstep on each row returned from the select using the Replace()
> function?
|||Hello,
Execute the sp_update_jobstep using the parameters @.Jobname,@.JobStep_id and
@.command. If you give an UNC path ensure that the SQL Server Startup account
have
rights to read and write that partcular share and directory.
Thanks
Hari
"Matt Williamson" <ih8spam@.spamsux.org> wrote in message
news:OCXSGq2LHHA.2028@.TK2MSFTNGP03.phx.gbl...
> Hari-
> I've been reading about the sp_update_jobstep proc in BOL but I'm not sure
> how can I take what is already there and change the command field to
> reflect the new UNC. The command line for that is huge and there are 2
> places that the UNC needs to be changed in it. Can I run a select
> statement and exec sp_update_jobstep on each row returned from the select
> using the Replace() function?
>
>
|||Thanks for the input. That's a non-issue though. It's already a UNC path.
I'm just changing it to a different one. The service that runs the job is
already a domain level account with appropriate permissions.
"NonNB" <nonnb@.webmail.co.za> wrote in message
news:1167905284.272432.183690@.i80g2000cwc.googlegr oups.com...
> One thing to note is that backing up to a UNC share has permission
> implications - you may need to change the credentials of the SQL Server
> and Agent services to a domain one.
> Regards
> Stuart
> Matt Williamson wrote:
>
|||> Execute the sp_update_jobstep using the parameters @.Jobname,@.JobStep_id
> and @.command. If you give an UNC path ensure that the SQL Server Startup
> account have
> rights to read and write that partcular share and directory.
Would there be any disadvantage to running something like this?
Update sysjobsteps
set command = replace(command, '\\OLDSERVER', '\\NEWSERVER')
where command like 'EXECUTE%'
In a test table that I created using a select from sysjobsteps, it worked
exactly how I wanted. I haven't tried it on the actual sysjobsteps table in
the msdb database yet though.