• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 710
  • Last Modified:

How to change a default Instance name in SQL Server

Hi All,
Can I change the name of the SQL Server Instance( either Default or Named) through some procedure?
0
saratcm
Asked:
saratcm
  • 3
  • 3
  • 2
  • +1
1 Solution
 
chapmandewCommented:
No, you cannot.  The default instance is the name of the machine...so, you'd need to change the machine name and then do some things to change the instance name.
0
 
DarksquireCommented:
You have to uninstall/reinstall to change an instance name. There is no other way that I know of.

http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/4fec5f6e-71c1-4f5b-a8be-fd94d15c89f1
0
 
saratcmAuthor Commented:
THANKS!
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Yes, you can change the Instance name of SQL Server both Default and Named instances..
Ideally this needs to be done in case of any Hostname or Servername changes and have a look at all the pointers mentioned in the link below:

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

If you feel this helped you out, kindly click on Request Attention button and assign points accordingly..
0
 
chapmandewCommented:
Pretty much exactly what I said in my first post.  :)
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Yes chapmandew, but since Re-installation approach was marked as answer I thought ( Hope I am wrong) that you agreed with that..
Since you haven't raised any objections, thought of objecting to that answer..

Kindly let me know if I am wrong.
0
 
chapmandewCommented:
You're wrong.  :)

Depending on the mood Im in, sometimes I just let the question go rather than object.
0
 
DarksquireCommented:
Yes you can, in SQL Server 2008. This question pertained to SQL Server 2005.
There is a "hack" method with stipulations below for SQL Server 2005
The sp_dropserver / sp_addserver method works only for the DEFAULT instance though. On the default instance only, by using sp_dropserver / sp_addserver you can change the server name reported by @@SERVERNAME. You would also have to change your machnine name if you wanted remote clients to be able to connect to that instance using that name.

For a NAMED (non-default) instance it isn't so easy. The supported method is to uninstall / re-install. There are registry hacks to rename a default instance, but you may prefer the safer option of re-installing:

1. drop current instname (sp_dropserver)
2. add new instance name (sp_addserver 'new name','local')
3. stop sqlserver
4. modify hklm\software\microsoft\microsoft sql server\
5. modify hklm\system\currentcontrolset\services\mssql$inst
6. use "sc create" to add new mssql$<inst>
7. start up with new inst_name - voila!

Note: you can do all this very safely. Instead of modifying the existing keys, you can create a new set. Modify this new set with appropriate changes (I'll see about putting an article together documenting it all). Register a new service for the new instance and then start it. Your original configuration/data is _never_ touched, so, if need to, you can always start up the old instance.

Info found at: http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/544c4eaf43ddfaf3/e9065e05718e984e

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> Depending on the mood Im in, sometimes I just let the question go rather than object.

Ok, Got it..

>> There are registry hacks to rename a default instance, but you may prefer the safer option of re-installing:

No registry hacks required for that as I have used only sp_dropserver and sp_addserver to change default instance name in SQL Server 2005 without any issues.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now