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.
| Author |
Topic |
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
 Posted - 2010-08-17 : 08:53:14
|
| Hi,Am in process of renaming the SQL server 2008 instance but am stuck while dealing with jobs. Renaming the SQL Server in SQL 2000step1 : exec sp_dropserver 'oldserver'exec sp_addserver 'newserver','local'step2 :restart the sql server and check whether below query output is same or not .select @@servername,serverproperty('servername')step3 : for updating jobs use msdbgoupdate sysjobs set originating_server = serverproperty('servername')go step4: Restart sql server and start using it.SQL Server 2008=================I wanted to do the same SQL 2008. This is what i have done but for jobs n all how to do i because in SQL 2008, we cannot make any direct updates and sysjobs is different in SQL 2008, we have the originating server id and not directly originating_server column where i can directly update the servername.step1 : exec sp_dropserver 'oldserver'exec sp_addserver 'newserver','local'step2 :restart the sql server and check whether below query output is same or not .select @@servername,serverproperty('servername')step3 : for updating jobs How to do it?????step4: If some linked servers, update master..sysservers set datasource = 'oldservername'where datasource = 'newservername'/*Msg 259, Level 16, State 1, Line 2Ad hoc updates to system catalogs are not allowed*/For this , i have dropped all the Linked Servers which are pointing to same machine(doesnt exist in real time but for testing)and recreated linked servers. For other Linked Servers which are pointing to other SQL instances i didnt disturb those.How can i rename or what steps to be followed in SQL 2008 to ensure, sql server renaming is complete.Note : Am not dealing with any Replication or Full text or anyother High Availability features. Just want to know what are the steps involved for sql server instance renaming whenever machine name is changed by network administrators or going for sql server machine migration?Thanks in Advance. |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
|
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
Posted - 2010-08-17 : 12:44:28
|
| i have gone through this link. It was good. What about sql jobs?? do we need to do anything more ?Thanks. |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2010-08-17 : 15:45:19
|
| to be 100% sure, try installing a sql server engine on your machine or dev environment and rename that one...the local jobs should be fine, but if there is a master job, you need to rename the linkedservers there as well...and to be safe...script out all the jobs in case you need to recreate them...better yet, make sure you have a working backup set for all system and user databases--------------------keeping it simple... |
|
|
|
|
|
|
|
|
Renaming the sql server 2008