28

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

Scott Marlowe
  • 423
  • 3
  • 6
  • 9

6 Answers6

31

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.

William Hilsum
  • 3,506
  • 5
  • 28
  • 39
  • 3
    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 02: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 02 '10 at 17:10
9

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

I got this information from the link below:

https://web.archive.org/web/20141116031942/http://modhul.com/2008/01/15/renaming-a-sql-server-instance/

Charles Roper
  • 459
  • 2
  • 7
  • 14
1

Just a quick note that you can add an "alias" and use it under both names:

enter image description here

jitbit
  • 407
  • 2
  • 6
  • 17
0

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.

slm
  • 7,355
  • 16
  • 54
  • 72
Ben
  • 1
-1

This blog seems to indicate it's quite easy.

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

EfficionDave
  • 211
  • 1
  • 4
  • 14
  • 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 02 '10 at 17:11
-4

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!

Nicolas Webb
  • 179
  • 1
  • 7