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?
Can I change the name of the SQL Server Instance( either Default or Named) through some procedure?
chapmandew
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
THANKS!
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..
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..
Pretty much exactly what I said in my first post. :)
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.
Since you haven't raised any objections, thought of objecting to that answer..
Kindly let me know if I am wrong.
You're wrong. :)
Depending on the mood Im in, sometimes I just let the question go rather than object.
Depending on the mood Im in, sometimes I just let the question go rather than object.
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\mi
5. modify hklm\system\currentcontrol
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
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\mi
5. modify hklm\system\currentcontrol
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
>> 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.
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.