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

Improve this question
0

6 Answers 6

Reset to default
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.

Improve this answer
2
  • 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, 2009 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, 2010 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/

Improve this answer
1

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

enter image description here

Improve this answer
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.

Improve this answer
-1

This blog seems to indicate it's quite easy.

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

Improve this answer
1
  • 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, 2010 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!

Improve this answer
3
  • 6
    I think that this deals with modifying a database name rather than the instance of SQL server
    – William Hilsum
    Sep 21, 2009 at 20:32
  • yeah... I want to rename the sql server engine instance, not just a table or database name.
    – Scott Marlowe
    Sep 21, 2009 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, 2009 at 20:54

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .