Post reply

Steps for changing a server name

  • August 25, 2010 at 2:25 pm

    #226218

    Hello All,

    We recently changed the name of the Windows 2008 server. The 2005 SQL Server EE seemed to have no problem adapting to the new name, but when I run the query: Select * from sys.servers, the "old" server name shows up as Server_id 0, the "new" server name is listed as Server_id 1, and the other linked servers appear normally.

    What do I need to do to remove the "old" box name from the system tables in SQL Server?

    Elliott

  • August 25, 2010 at 2:33 pm

    #1211936

    sp_addserver 'NewServerName', 'local'

    You must shut down and restart the server.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • August 25, 2010 at 2:34 pm

    #1211937

    Also you may have to script out and recreate your jobs, and potentially logins (they have the server name hardcoded).

  • August 25, 2010 at 2:36 pm

    #1211940

    Thanks Wayne and Derrick

    I'll give that a try.

    Elliott

  • August 25, 2010 at 2:42 pm

    #1211947

    The addserver command did not work: "The server xxxx already exists"

    Which is true. The new server name has been recognized by SQL Server and the jobs, procs, mirroring, etc. are all working fine.

    The problem is in getting the old name out of sys.servers.

    Any other ideas?

    Elliott

  • August 25, 2010 at 2:47 pm

    #1211953

    Do sp_dropserver 'oldname' first to drop the old name, then add the new one with 'local'

  • August 25, 2010 at 2:57 pm

    #1211959

    BINGO!

    Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply