Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

How can I rename a SQL Server 2008 instance without reinstalling?

For example, if the db is referenced as "MySQLServer\MSSQL2008", how can I rename to "MySQLServer\SQL2008"?

share|improve this question
add comment

migrated from superuser.com Sep 21 '09 at 22:38

This question came from our site for computer enthusiasts and power users.

5 Answers

up vote 22 down vote accepted

I don't think it is possible to rename without installing.

There are traces left to the name in a few internal databases such as replication and you may find errors later on.

If you can, unless you have more than one instance, you are best off reinstalling and then importing all your databases again.

share|improve this answer
2  
Wil is correctly. There's no way to rename the instance. You have to uninstall the instance, and then install a new instance. (Or install the new instance, move the databases to the new instance, then disable or uninstall the old instance). –  mrdenny Sep 22 '09 at 2:44
    
doing the uninstall and then re-installing with a named instance isn't really very hard. just hold onto the Data directory (and maybe the Backups directory) in the old instance. –  djangofan Jan 2 '10 at 17:10
add comment

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.

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.

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 service.

I got this information from the link below:

Read more: http://www.modhul.com/2008/01/15/renaming-a-sql-server-instance/#ixzz0tRlkLec4

share|improve this answer
add comment

select @@servername will show the new name but server name in ERRORLOG will show the old name and you cannot connect using the new name.

The only solution is to re-install.

share|improve this answer
add comment

This blog seems to indicate it's quite easy.

http://www.modhul.com/2008/01/15/renaming-a-sql-server-instance/

share|improve this answer
3  
but it fails to rename the instance directory. that could cause some confusion to some people. i wouldn't recommend that method, even if its easy. –  djangofan Jan 2 '10 at 17:11
add comment

You'd want to use ALTER DATABASE.

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

In this situation, for your example above, I'd try:

ALTER DATABASE MSSQL2008 MODIFY NAME = 'SQL2008'

Good luck!

share|improve this answer
6  
I think that this deals with modifying a database name rather than the instance of SQL server –  William Hilsum Sep 21 '09 at 20:32
    
yeah... I want to rename the sql server engine instance, not just a table or database name. –  scottmarlowe Sep 21 '09 at 20:35
    
You're correct. And I agree with @Wil - I'm not aware of a way to do this without reinstallation. –  Nicolas Webb Sep 21 '09 at 20:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.