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.
Showing posts with label easiest. Show all posts
Showing posts with label easiest. Show all posts
Sunday, March 11, 2012
Thursday, March 8, 2012
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:
> >
> > 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?|||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...
> >
>> 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?
>
>|||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.googlegroups.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:
>> >
>> > 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?
>|||> 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.
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:
> >
> > 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?|||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...
> >
>> 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?
>
>|||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.googlegroups.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:
>> >
>> > 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?
>|||> 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.
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
MattHello,
Take a look into the system stored procedure sp_update_jobstep in MSDB
database.
Thanks
Hari
"Matt Williamson" wrote:
> 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 th
e
> 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 refle
ct
> 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.googlegroups.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.
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
MattHello,
Take a look into the system stored procedure sp_update_jobstep in MSDB
database.
Thanks
Hari
"Matt Williamson" wrote:
> 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 th
e
> 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 refle
ct
> 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.googlegroups.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.
Subscribe to:
Posts (Atom)