February 16, 2005 at 11:38 am
I know how to rename a SQL server, but how do I rename another instance of SQL server 2000.
Thanks
February 16, 2005 at 11:57 am
AFAIK, there's no way to rename a SQL Server instance. You either have to re-install, or install a new instance with the desired name. Once that's done, you could move your user DB's over. Hope this helps.
My hovercraft is full of eels.
March 16, 2005 at 12:41 pm
it is possible to rename the named instance of SQL server 2K, first use sp_dropserver and sp_addserver (drop the old server name and add new server name in sysservers table). Turn down the instance and create the same folder structure as the old installation folder for this renamed instance, both for installation folders and data folder appropriately i.e $OldInstanceName to $NewName, also search and replace this old instance name in registery.
then run the "rebuildm" utility for rebuilding the system databases now reboot the system and make sure SQL server is running under new name. Then detach and attache the user databases to the new location (new folder i.e $NewName)
March 16, 2005 at 1:20 pm
The shortest path is Uninstall/Reinstall (Easier than any other route)
For confirmation http://support.microsoft.com/kb/260414/en-us
* Noel
August 18, 2008 at 7:49 pm
noeld (3/16/2005)
The shortest path is Uninstall/Reinstall (Easier than any other route)
For confirmation
Since this appears quite high in the google search for 'rename sql instance'..and is just not right IMHO, I thought I better add a comment.
sp_dropserver "oldservername"
sp_addserver "newservername" , local
done.
I cant see how uninstalling and reinstalling is easier for you than 2 queries and a few minor clicks here and there....
September 30, 2008 at 7:59 am
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:
October 21, 2009 at 3:45 am
– Get the current name of the SQL Server instance for later comparison.
SELECT @@servername
– Remove server from the list of known remote and linked servers on the local instance of SQL Server.
EXEC master.dbo.sp_dropserver ‘[SERVER NAME]‘
– Define the name of the local instance of SQL Server.
EXEC master.dbo.sp_addserver ‘[NEW SERVER NAME]‘, ‘local’
– Get the new name of the SQL Server instance for comparison.
SELECT @@servername
March 23, 2012 at 9:20 am
Rao.V (10/21/2009)
– Get the current name of the SQL Server instance for later comparison.SELECT @@servername
– Remove server from the list of known remote and linked servers on the local instance of SQL Server.
EXEC master.dbo.sp_dropserver ‘[SERVER NAME]‘
– Define the name of the local instance of SQL Server.
EXEC master.dbo.sp_addserver ‘[NEW SERVER NAME]‘, ‘local’
– Get the new name of the SQL Server instance for comparison.
SELECT @@servername
I know this is an old post, but...
After renaming the instance using sp_dropserver and sp_addserver, selecting @@SERVERNAME will not return the new, changed instance name until a sql server service restart has occurred.
_________________________________
seth delconte
http://sqlkeys.com
August 14, 2012 at 12:07 pm
did you do it for a default or name instance?
October 23, 2012 at 2:56 pm
Muy buen post, funcionó de maravilla, me han aliviado de un gran problema.
Gracias
Atte.
Hubert Calderón Vargas
May 27, 2015 at 7:11 am
Old post, but new day...
We have an existing named instance of SQL Express. We want to replace it with a full named SQL instance in such a way that it does not affect the application side of the server. Our thought was to install a second named instance, copy the databases from the Express version to the full version, remove (or rename)the Old Express named instance, then use sp_dropserver and sp_addserver process to change the full version SQL named instance to the old express named instance, Stop and restart services. Would that work?
The point may be moot as we are probably going to try it and see if it does work. This is with SQL 2012, by the way.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply