locked
Rename sql server 2008 RRS feed

  • Question

  • Hi,

    Am in process of renaming the SQL server 2008 instance but am stuck while dealing with jobs.

    Renaming the SQL Server in SQL 2000

    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
    use msdb
    go
    update 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 2
    Ad 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.
    Tuesday, August 17, 2010 12:57 PM

Answers

All replies