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

26 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
    Replies
    1. Vinoth - Does it have to be a Hostname renamed as well in order for a new connection to connect to the newly changed name?

      Delete
    2. Vinoth - Does it have to be a Hostname renamed as well in order for a new connection to connect to the newly changed name?

      Delete
  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. It worked perfectly for me, good thing that I searched it out first since I could not get it started. Thanks!

    ReplyDelete
  7. 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
  8. 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
  9. Typos on post:

    type -> typo
    loop -> look

    Sorry

    ReplyDelete
  10. 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
  11. What about SSIS, will it be changed automatically or do we need to change it manually? if so how?

    ReplyDelete
  12. Steven K. Mariner2 November 2017 at 06:17

    I have never gotten this procedure to work correctly to rename only the instance portion of the name.
    e.g., changing [PRODSQL\SQL2008R2] to [PRODSQL\SQL2016ENT] and then directing SSMS to connect to [PRODSQL\SQL2016ENT] fails.

    After the rename, and the commensurate restarts, I can still connect to [PRODSQL\SQL2008R2], and it happily reports @@SEVERNAME as PRODSQL\SQL2016ENT; but attempts to connect to that new instance name fail and connections to the old instance name work.

    Yes, restarted SQL Services.
    Yes, restarted system.
    Yes, tried Alias.
    No, it's never worked.
    I've dumped the Registry and I've never had the time to try to hack the change into all those entries to see if that would fix it.

    Ultimately, I find someone in each thread which says you can't actually rename the instance.

    You may be able to rename the server portion of the full instance name, but the instance name itself seems to be fairly well intractable.

    It occurs to me that the SETUP.EXE for SQL Server should have an option on the menu to Rename a SQL Instance. But if they haven't done it yet, after decades of people asking for it, my bet is it will not happen.

    So if the instance name is meant to reflect the SQL version, the best bet still seems to be to do a fresh side-by-side install of the new version into its own instance, then back up all databases from the old instance and restore them to the new instance; port the LOGINS, undo and redo all the Replication, reconfigure SSAS and SSRS, and all that horrible stuff we've had to do for decades.

    Unless someone can show me what I'm mising...

    ReplyDelete
  13. Notwithstanding regardless of whether your webpage is a huge organization special device or perhaps a region to run you and your family illustrations for the entire family to show cross country you proceed to are probably going to must have a web have. https://onohosting.com/

    ReplyDelete
  14. If you are planning to host your own website, there will be many decisions that you will have to take. One of the most crucial decisions will be to select a web hosting solution for your website. This is a very important decision as it can influence the way your website performs. https://onohosting.com/

    ReplyDelete
  15. TODAY I GOT MY DESIRED XMAS LOAN AMOUNT $520,000.00 FROM A RELIABLE AND TRUSTED LOAN COMPANY. IF YOU NEED A LOAN NOW EMAIL CONTACT drbenjaminfinance@gmail.com

    Hello, I'm here to testify of how i got my loan from BENJAMIN LOAN FINANCE(drbenjaminfinance@gmail.com) I don't know if you are in need of an urgent loan to pay bills, start business or build a house, they offer all kinds of loan. So feel free to contact Dr. Benjamin Owen he holds all of the information about how to obtain money quickly and painlessly without cost/stress via Email: drbenjaminfinance@gmail.com

    Consider all your financial problems tackled and solved ASAP. Share this to help a soul right now THANKS.

    ReplyDelete
  16. birthday wishes for colleagues . Hope these birthday wishes for colleagues and coworkers will help you to write a sweet birthday message and images.

    ReplyDelete
  17. Get Custom Boxes with Logo At 25% Off.Very Easy to Order Any Quantities Any Size. There’s no job that’s too big, too small, or too complex for us to handle. Bring your packaging projects to us, and we’ll respond with an enthusiastic “PackagingPapa!” Customized Boxes With Logo

    ReplyDelete
  18. Custom Packaging Boxes with LOGO!We pay attention to every detail and work hard to offer the high quality you expect. We have the experience, the equipment, and just about all the services you’d expect from an old-fashioned printer. But with PackagingPapa, all of this is available online, at your convenience. Customized Boxes With Logo

    ReplyDelete