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.

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

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 want to check out
http://www.karaszi.com/SQLServer/info_change_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 & 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.

Restore master backup from different server causes file location problems

Hi,
I have just restored a backup of our production master database to a
development PC as a test and now SQL Server won't start. This is
because it cannot open the devices for the various databases. Fair
enough, I understand why that is - we have system dbs (model, msdb) on
D: drive in production while they are on C: drive on dev PC.
My questions are: -
1. Is it possible to repair this by somehow updating the server's links
to where the database files are? I know you can specify master db/log
files' location on command line, but not the other system DBs.
2. Is it only the device activation error for the system databases that
prevent SQL Server restarting or do all user database files need to
also be in their correct location?
I can't get SQL up and running because there's no D: drive on the dev
PC, so I can't move the files into the correct location for SQL Server
to find them on startup...
Rgds,
Andrew Duncan
SQL Server will start once the system databases are set
correctly. The following articles have information on
changing the file locations for system databases:
Moving SQL Server databases to a new location with
Detach/Attach
http://support.microsoft.com/?id=224071
INF: Moving SQL Server 7.0 Databases to a New Server with
BACKUP and RESTORE
http://support.microsoft.com/?id=304692
-Sue
On 16 Mar 2005 00:10:03 -0800, andrew@.duncanhcmc.com wrote:

>Hi,
>I have just restored a backup of our production master database to a
>development PC as a test and now SQL Server won't start. This is
>because it cannot open the devices for the various databases. Fair
>enough, I understand why that is - we have system dbs (model, msdb) on
>D: drive in production while they are on C: drive on dev PC.
>My questions are: -
>1. Is it possible to repair this by somehow updating the server's links
>to where the database files are? I know you can specify master db/log
>files' location on command line, but not the other system DBs.
>2. Is it only the device activation error for the system databases that
>prevent SQL Server restarting or do all user database files need to
>also be in their correct location?
>I can't get SQL up and running because there's no D: drive on the dev
>PC, so I can't move the files into the correct location for SQL Server
>to find them on startup...
>Rgds,
>Andrew Duncan
|||Thanks Sue,
I actually had this article but for some reason assumed I couldn't move
the files unless they were already in a valid location!
One point not mentioned in the article was I had to start sqlservr with
-f flag due to tempdb files not being found. Only then could I perform
the ALTER DATABASE commands to move tempdb.
Thanks again for response!
|||The -f flag is mentioned several times in the second
article. It's a more inclusive article but there are a few
differences with SQL 7 and 2000 and the first article is
what MS has for SQL Server 2000. I'd prefer that they just
update the second article on using backup and restore with
SQL Server 2000.
-Sue
On 16 Mar 2005 18:17:38 -0800, andrew@.duncanhcmc.com wrote:

>Thanks Sue,
>I actually had this article but for some reason assumed I couldn't move
>the files unless they were already in a valid location!
>One point not mentioned in the article was I had to start sqlservr with
>-f flag due to tempdb files not being found. Only then could I perform
>the ALTER DATABASE commands to move tempdb.
>Thanks again for response!

Restore master backup from different server causes file location problems

Hi,
I have just restored a backup of our production master database to a
development PC as a test and now SQL Server won't start. This is
because it cannot open the devices for the various databases. Fair
enough, I understand why that is - we have system dbs (model, msdb) on
D: drive in production while they are on C: drive on dev PC.
My questions are: -
1. Is it possible to repair this by somehow updating the server's links
to where the database files are? I know you can specify master db/log
files' location on command line, but not the other system DBs.
2. Is it only the device activation error for the system databases that
prevent SQL Server restarting or do all user database files need to
also be in their correct location?
I can't get SQL up and running because there's no D: drive on the dev
PC, so I can't move the files into the correct location for SQL Server
to find them on startup...
Rgds,
Andrew DuncanSQL Server will start once the system databases are set
correctly. The following articles have information on
changing the file locations for system databases:
Moving SQL Server databases to a new location with
Detach/Attach
http://support.microsoft.com/?id=224071
INF: Moving SQL Server 7.0 Databases to a New Server with
BACKUP and RESTORE
http://support.microsoft.com/?id=304692
-Sue
On 16 Mar 2005 00:10:03 -0800, andrew@.duncanhcmc.com wrote:
>Hi,
>I have just restored a backup of our production master database to a
>development PC as a test and now SQL Server won't start. This is
>because it cannot open the devices for the various databases. Fair
>enough, I understand why that is - we have system dbs (model, msdb) on
>D: drive in production while they are on C: drive on dev PC.
>My questions are: -
>1. Is it possible to repair this by somehow updating the server's links
>to where the database files are? I know you can specify master db/log
>files' location on command line, but not the other system DBs.
>2. Is it only the device activation error for the system databases that
>prevent SQL Server restarting or do all user database files need to
>also be in their correct location?
>I can't get SQL up and running because there's no D: drive on the dev
>PC, so I can't move the files into the correct location for SQL Server
>to find them on startup...
>Rgds,
>Andrew Duncan|||Thanks Sue,
I actually had this article but for some reason assumed I couldn't move
the files unless they were already in a valid location!
One point not mentioned in the article was I had to start sqlservr with
-f flag due to tempdb files not being found. Only then could I perform
the ALTER DATABASE commands to move tempdb.
Thanks again for response!|||The -f flag is mentioned several times in the second
article. It's a more inclusive article but there are a few
differences with SQL 7 and 2000 and the first article is
what MS has for SQL Server 2000. I'd prefer that they just
update the second article on using backup and restore with
SQL Server 2000.
-Sue
On 16 Mar 2005 18:17:38 -0800, andrew@.duncanhcmc.com wrote:
>Thanks Sue,
>I actually had this article but for some reason assumed I couldn't move
>the files unless they were already in a valid location!
>One point not mentioned in the article was I had to start sqlservr with
>-f flag due to tempdb files not being found. Only then could I perform
>the ALTER DATABASE commands to move tempdb.
>Thanks again for response!

Restore master backup from different server causes file location problems

Hi,
I have just restored a backup of our production master database to a
development PC as a test and now SQL Server won't start. This is
because it cannot open the devices for the various databases. Fair
enough, I understand why that is - we have system dbs (model, msdb) on
D: drive in production while they are on C: drive on dev PC.
My questions are: -
1. Is it possible to repair this by somehow updating the server's links
to where the database files are? I know you can specify master db/log
files' location on command line, but not the other system DBs.
2. Is it only the device activation error for the system databases that
prevent SQL Server restarting or do all user database files need to
also be in their correct location?
I can't get SQL up and running because there's no D: drive on the dev
PC, so I can't move the files into the correct location for SQL Server
to find them on startup...
Rgds,
Andrew DuncanSQL Server will start once the system databases are set
correctly. The following articles have information on
changing the file locations for system databases:
Moving SQL Server databases to a new location with
Detach/Attach
http://support.microsoft.com/?id=224071
INF: Moving SQL Server 7.0 Databases to a New Server with
BACKUP and RESTORE
http://support.microsoft.com/?id=304692
-Sue
On 16 Mar 2005 00:10:03 -0800, andrew@.duncanhcmc.com wrote:

>Hi,
>I have just restored a backup of our production master database to a
>development PC as a test and now SQL Server won't start. This is
>because it cannot open the devices for the various databases. Fair
>enough, I understand why that is - we have system dbs (model, msdb) on
>D: drive in production while they are on C: drive on dev PC.
>My questions are: -
>1. Is it possible to repair this by somehow updating the server's links
>to where the database files are? I know you can specify master db/log
>files' location on command line, but not the other system DBs.
>2. Is it only the device activation error for the system databases that
>prevent SQL Server restarting or do all user database files need to
>also be in their correct location?
>I can't get SQL up and running because there's no D: drive on the dev
>PC, so I can't move the files into the correct location for SQL Server
>to find them on startup...
>Rgds,
>Andrew Duncan|||Thanks Sue,
I actually had this article but for some reason assumed I couldn't move
the files unless they were already in a valid location!
One point not mentioned in the article was I had to start sqlservr with
-f flag due to tempdb files not being found. Only then could I perform
the ALTER DATABASE commands to move tempdb.
Thanks again for response!|||The -f flag is mentioned several times in the second
article. It's a more inclusive article but there are a few
differences with SQL 7 and 2000 and the first article is
what MS has for SQL Server 2000. I'd prefer that they just
update the second article on using backup and restore with
SQL Server 2000.
-Sue
On 16 Mar 2005 18:17:38 -0800, andrew@.duncanhcmc.com wrote:

>Thanks Sue,
>I actually had this article but for some reason assumed I couldn't move
>the files unless they were already in a valid location!
>One point not mentioned in the article was I had to start sqlservr with
>-f flag due to tempdb files not being found. Only then could I perform
>the ALTER DATABASE commands to move tempdb.
>Thanks again for response!