Few Problems after Restoring Backup

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Few Problems after Restoring Backup

Author

Topic

hiuhiu
Starting Member

18 Posts

Posted - 2004-01-14 : 22:43:49

Hi,

A few problems after I've restored the backup from the old SQL server to the new SQL server.

I did the following steps (in sequence):

1) Restore master from the FULL backup of the original server
2) Restore msdb from the FULL backup of the original server
3) Restore all user databases from the FULL backup of the original server

It works OK but it won't allow me to delete any job in the new server with the following error:

Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.

My questions:

1) How do I correct the above problem?
2) If I use Enterprise Manager and double click a job, it shows the Source as the name of the old server, is that normal?
3) If I restore the user databases from the FULL backup, do I still need to restore the transaction log backup? I tried both but didn't see any difference in the data.

Thanks for any help.

HIU

nr
SQLTeam MVY

12543 Posts

Posted - 2004-01-15 : 04:59:45

I've come across this before but can't remember much about it.
In msdb..sysjobs is a originating_server field. Maybe updating this will cure it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

franco
Constraint Violating Yak Guru

255 Posts

Posted - 2004-01-15 : 09:10:41

here is the answer to your first question:
Microsoft Knowledge Base Article - 281642

Rename the server back to the original name.
Script out all of the jobs and then delete them.
Rename the server to the new name.
Add back the jobs by running the script generated from step 2.
HTH

Franco

hiuhiu
Starting Member

18 Posts

Posted - 2004-01-15 : 19:57:10

Thanks! But how can I rename the Server Name? I am sorry I have troubles going to the Microsoft website in office (not blocking, but extremely sloooow for some unknown reasons).

And any answer to my third question? Probably a stupid question but it's the most important thing that needs to be confirmed by experts like you guys.

Thanks!

HIU

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-15 : 20:42:37

Is your SQL Server Books Online slow too?

From SQL Server Books Online:

Renaming a Server
When you change the name of the computer that is running Microsoft® SQL Server™ 2000, the new name is recognized during SQL Server startup. You do not have to run Setup again to reset the computer name.

You can connect to SQL Server using the new computer name after you have restarted the server. However, to correct the sysservers system table, you should manually run these procedures:

sp_dropserver old_name
GO
sp_addserver new_name, local
GO

When you have more than one instance of SQL Server on the computer, change the sysservers system table information by running the stored procedures this way:

sp_dropserver old_servername\instancename
GO
sp_addserver new_servername\instancename, local
GO

************

You only have to restore the transaction log if you want the data that was modified after the full backup completed. If you ran a full backup prior to the move from the old server and no one else touched the source database, then all you need is the full backup.

Tara

hiuhiu
Starting Member

18 Posts

Posted - 2004-01-16 : 01:11:31

Thanks again, tduggan! I kept on looking the menu bar in the Enterprise Manager trying to find a function that will change the server name. I will try my best to search for solution before posting questions in the future. Maybe my brain is slow too!

OK! I think all I need is a full backup since I will move my databases to the new server in the next holiday when nobody will work except me!

Thank you so much.

HIU

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-01-16 : 13:41:59

DO NOT do all this stuff. If you just update the originating_server to the new server name you will be fine. This is an updatable table, regardless of whether you have option chosen to not allow sytem table updates for this very reason.

All that does is refer to the MSX server, which you can change. We have migrated several servers to different names and it's standard procedure. It will save you a lot of headache.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-01-16 : 13:43:45

It won't hurt you to follow what tduggan said. Just don't do the whole delete/script jobs thing. Changing the MSX settings will work for this.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources