Tuesday, 25 September 2012

How to Rename SQL Server

With the release of SQL 2012 most of the companies might have started migration of SQL server to new version, In case of server migrations most of the time we will end up in a situation to rename the physical server hosting SQL Server, Renaming SQL Server to host the new physical server name is pretty simple as done below

MSDN Reference for Add Server http://msdn.microsoft.com/en-us/library/ms174411.aspx
As said in the article, This procedure must be executed on all instances of the Database Engine hosted on the computer. The instance name of the Database Engine cannot be changed.
 
On the SQL Server instance, run the following query.
sp_dropserver '<oldservername>'
go

sp_addserver '<newservername>', local
go

Now restart the SQL Server related services. Then, run @@servername to verify if the new name has been updated.
select @@servername

18 comments :

  1. This is really valuable information! Are there any side effects to doing this? Just wondering because I've never seen this documented anywhere but it's definitely very useful.

    ReplyDelete
    Replies
    1. Hi, We have been using the procedure for many years now and we haven't faced any issue. sp_dropserver and sp_addserver are well documented sp's in MSDN.

      Delete
  2. sp_addserver discountined in 2012. fail

    ReplyDelete
    Replies
    1. Hi, The sp_addserver works fine for local server and is documented in SQL Server 2012

      http://msdn.microsoft.com/en-us/library/ms174411.aspx

      Microsoft has deprecated sp_addserver only for remote servers. More details explained on the Nakul's blog

      http://beyondrelational.com/modules/2/blogs/77/Posts/14883/0164-sql-server-2012-discontinued-features-spaddserver-remote-server-registration-msg-15663.aspx.

      Delete
    2. That link is dead. Please submit new link, thanks!

      Delete
    3. https://msdn.microsoft.com/en-us/library/ms143799.aspx

      Delete
  3. Hi. I have done this, changing from DBSERVER1 to DBSERVER. I used "select @@servername" to confirm it completed successfully. However, when I use Management Studio or Profiler, I cannot connect to DBSERVER, I can only connect to DBSERVER1. Is there something else I need to do? Thanks

    ReplyDelete
  4. A restart of SQL related services after SQL rename should take care of connection remotely. was the Services restarted?

    ReplyDelete
  5. question- I had 2008 R2 installed and by mistake when installing 2012 I had them use smae instance name (server name).
    now all DBs from 2008 showing up in 2012, how can I change ONLY the 2021 instance name without affecting the 2008?

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. Thanks for sharing this such a great information about Chossing stream host. i really inspire your post i shared this link to my all facebook and twitter friends because this link helpfull for everyone

    ReplyDelete
  8. It worked perfectly for me, good thing that I searched it out first since I could not get it started. Thanks!

    ReplyDelete
  9. Great article! Very informative and simple easy to understand. Keep posting it really help for those people that needs this. I will refer your blog to my friends. Thanks a lot!

    ReplyDelete
  10. This procedure did not work for me with SQL Server 2012 Express on an SBS 2008 server. I originally had a type in the instance name. Connected to the instance using Management Studio 2012, opened a Query window, I corrected it using this procedure, and got the @@servername confirmation. I then restarted the server rather than just the services. After restart the services still showed the old instance name as well as the list of servers in Management Studio. If I go into the old instance name and loop, @@servername shows the new name. The named pipe shows the old name.

    As a stab, I tried changing it to the new name with no change in behavior.

    What am I missing?

    Thanks

    Joe M

    ReplyDelete
    Replies
    1. Sorry - Not gaining on typos:

      type -> typo
      loop -> look
      I changed the named pipe, not "it".

      Delete
    2. Hi,

      As i have already mentioned in the article i am afraid you can't change the instance name, This procedure is only change your computer name and not instance name.

      -Vinoth.

      Delete
  11. Typos on post:

    type -> typo
    loop -> look

    Sorry

    ReplyDelete
  12. You can also take the help of following link to rename SQL Server database using SQL Server Management Studio and Transact-SQL: http://www.sqlserverlogexplorer.com/how-to-rename-a-database/

    ReplyDelete