Monday, March 26, 2012

restore to new server causes problems

SQL 2000 sp4 on both servers
I backed up all databases from server A.
Installed SQL Server on Server B then restored all databases (system &
user db's) to server B
Shut down SQL Server on server A
(on server B from here on)
When I try to change maintenance plan properties (the path for text
reports no longer exists), I get the error
"Error 14274: Cannot add, update, or delete a job (or its steps or
schedules) that originated from an MSX server."
Looking that up, I find article # 281642. That article says the
workaround is:
1. Rename the server back to original name
2. script out all of the jobs and then delete them
3. rename the server to the new name.
4. Add back the jobs by running the script generated in step 2.
1st question: Can I just
Update sysjobs
Set originating_server = 'server B'
where originating_server = 'server A'
2nd question:
Do I need to be concerned about the server name being wrong in
master..sysservers?
There are 3 columns, srvname, datasource & srvnetname that all have the
original server name.
3rd question:
All sql server agent jobs on server B have enabled = no (in SEM).
Select enabled from sysjobs and sp_help_job confirm this. The jobs
(trans log and full backups) continue to run and appear to be successful.
Why do all the jobs continue to run when they are disabled?
Thanks
Tom
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
This sounds right. I did something like this several years ago with no ill
effect. Id say try it out on just 1 job and see what happens, but thats
sounds like it to me.

> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
I've never been worried about it when I took your same actions, but perhaps
I just got lucky.
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:%23M%23C6WvEHHA.3660@.TK2MSFTNGP06.phx.gbl...
> SQL 2000 sp4 on both servers
> I backed up all databases from server A.
> Installed SQL Server on Server B then restored all databases (system &
> user db's) to server B
> Shut down SQL Server on server A
> (on server B from here on)
> When I try to change maintenance plan properties (the path for text
> reports no longer exists), I get the error
> "Error 14274: Cannot add, update, or delete a job (or its steps or
> schedules) that originated from an MSX server."
> Looking that up, I find article # 281642. That article says the
> workaround is:
> 1. Rename the server back to original name
> 2. script out all of the jobs and then delete them
> 3. rename the server to the new name.
> 4. Add back the jobs by running the script generated in step 2.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
>
> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
>
> 3rd question:
> All sql server agent jobs on server B have enabled = no (in SEM).
> Select enabled from sysjobs and sp_help_job confirm this. The jobs
> (trans log and full backups) continue to run and appear to be successful.
> Why do all the jobs continue to run when they are disabled?
>
> Thanks
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.|||What you did pretty much resembles a rename of the machine, so you might wan
t to check out
http://www.karaszi.com/SQLServer/in...server_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:%23M%23C6WvEHHA.3660@.TK2MSFTNGP06.phx.gbl...
> SQL 2000 sp4 on both servers
> I backed up all databases from server A.
> Installed SQL Server on Server B then restored all databases (system & use
r db's) to server B
> Shut down SQL Server on server A
> (on server B from here on)
> When I try to change maintenance plan properties (the path for text report
s no longer exists), I
> get the error "Error 14274: Cannot add, update, or delete a job (or its st
eps or schedules) that
> originated from an MSX server."
> Looking that up, I find article # 281642. That article says the workaroun
d is:
> 1. Rename the server back to original name
> 2. script out all of the jobs and then delete them
> 3. rename the server to the new name.
> 4. Add back the jobs by running the script generated in step 2.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
>
> 2nd question:
> Do I need to be concerned about the server name being wrong in master..sys
servers? There are 3
> columns, srvname, datasource & srvnetname that all have the original serve
r name.
>
> 3rd question:
> All sql server agent jobs on server B have enabled = no (in SEM). Select
enabled from sysjobs and
> sp_help_job confirm this. The jobs (trans log and full backups) continue
to run and appear to be
> successful.
> Why do all the jobs continue to run when they are disabled?
>
> Thanks
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.sql

No comments:

Post a Comment