Solved

Change SQLSERVER Instance name

Posted on 2014-12-02
6
94 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
  • 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 45

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 45

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 45

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now