Impact of SQL Server name change

  • What would be the impact of SQL server name change


    if i use sp_dropserver and sp_addserver may i need to recreate all my jobs,logins,users etc. etc.


    Thnks for any help !

  • the impact is very minimal; only if you replace a server does it require recreating logins/jobs/etc.

    typically, you'd right click on my computer>>Properties>>Network Identification and give the computer a new name. then, after it reboots, you'd do exactly as you described:

    sp_dropserver 'old server name'

    sp_addserver 'new server name', 'local'

    you won't have to recreate any of the items you mentioned. those items are stored in the master database or msdb database, and are not referenced in any way by servername

    the only real change you would see is if you did select @@servername, it would have tne new value


    Instances would be handled automatically, ie if you had installed any instances of SQL2K or SQL 2005, then people accessing them would simply change from serveroldname\instancename to servernewname\instancename, still witht he same username/passwords that they were using previously.




    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I did this recently and had problems when returning @@servername as the old server name kept srvid 0 in master..sysservers and the new servername was srvid 1.   I'm opting for reinstalling SQL on our production server next time i do a server move!

  • Thanks a Ton............ Lowell !

Viewing 4 posts - 1 through 4 (of 4 total)

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