Solved

Change SQLSERVER Instance name

Posted on 2014-12-02
6
110 Views
Last Modified: 2015-05-23
Hi I have Cloned Windows VM Server: A and Named as Server: B, Later I changed the OS Hostname from A to B.

Now I required to rename the SQL Server instance name from A to B.

I did rename by using sp_dropserver & sp_addserver, but could not login after rename from A to B.

Need help to rename the SQL Server instance from A to B
0
Comment
Question by:nssasikumar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 25

Expert Comment

by:SStory
ID: 40476681
You can rename host name of instance like follows:
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
(source: http://www.sqltechnet.com/2012/09/how-to-rename-sql-server-instance.html)
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40478033
How did you rename the SQL Server instance and what's the error now?
0
 

Author Comment

by:nssasikumar
ID: 40783115
I have tried the Steps, mentioned above but faced some issues, So I fall back to Old Hostname
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40783125
If it's a default MSSQL instance you shouldn't have any kind of problem renaming it.
But if it's a named instance you just can't. You'll need to uninstall the old instance and install a new one.
0
 

Author Comment

by:nssasikumar
ID: 40783146
It means after Host renaming at OS level, the SQLServer Default Instance will be auto renamed as Per New OS hostname?
0
 
LVL 51

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40783171
SQLServer Default Instance will be auto renamed
No auto renamed. Default instances aren't named so you just need to drop the entry from the old server and add a new entry for the new server:
sp_dropserver '<oldservername>'
 go

 sp_addserver '<newservername>', local
 go

Open in new window

Don't forget to restart the SQL Server so it can use the new server name.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question