How to rename a SQL Server 2008 instance?

Question

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"?

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

This blog seems to indicate it's quite easy.

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

Answer 5

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!