I know that this script is ubiquitous across the ‘net, but whenever I google for it I come up with elaborate stored proc’s that are overkill for my needs – so here are the commands necessary to rename a SQL Server instance, for posterity:
– 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
And a few notes relating to their usage:
- sp_dropserver: 1. this stored procedure can remove both remote and linked servers; 2. using the droplogins parameter indicates that related remote and linked server logins for [SERVER NAME] must also be removed. More info.
- sp_addserver: 1. to define a linked server, use sp_addlinkedserver (as this functionality will be depreciated in version above SQL Server 2005); 2. sp_addserver cannot be used inside a user-defined transaction. More info.
I would also recommend the following when renaming a SQL Server:
- If you are performing this task as part of a machine rename, rename the machine first then rename the SQL Server.
- Once you have renamed the SQL Server, do a full restart of SQLServer, including any dependant services.
![Reblog this post [with Zemanta]](http://img.zemanta.com/reblog_e.png?x-id=aeb1e325-33dd-45cb-bca7-2510d6682e8a)
thank’s a lot, i was try the command and it’s work !!!!
Hello, I tried it..
If I do this: “select * from sys.servers” it looks good, but when I try to log on to the new INSTANCE name it won’t work.. the old one I can still connect to. I did a reboot of the system..
What am I missing here?
Same problem here …
Kosm/Pavel,
I’m sure you did, but can you confirm that you successfully ‘dropped the server’ before adding a new server?
Nick.
I just tried it. select @@servername shows the new name. I re-booted the machine and logged back in. When I use management studio and tell it to browse for instances on the local machine, it still shows the old name, even though @@servername is what I want to see…
I did the drop server and it worked, so help!
Thanks,
Mark
Same happened to me.
I have been searching the web for weeks now on how to install SQL Server Express as a named instance and all the config.ini settings I tried and command prompt params I tried does not work. Can anyone help with a sample .ini file that actually works. At this stage it only opens up the command prompt if I try the .ini file and nothing happens
Same problem for me… I follow the instructions and in SS Configuration Manager the old name still appears. You migt as well remove this post from the Internet since it doesn’t work.
i wouldn’t recommend following this blog article. the blog article fails to update the sql instance directory name (as well as the references inside each database to that directory).
LOL @mozkill what a shoot down and yes i concur.