How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server 2005

When you change the name of the computer that is running Microsoft SQL Server 2005, the new name is recognized during SQL Server startup. You do not have to run Setup again to reset the computer name. The following steps cannot be used to rename an instance of SQL Server 2005. These steps can be used only to rename the part of the instance name that corresponds to the computer name. For example, you can change a computer named MB1 that hosts an instance of SQL Server named Instance1 to another name, such as MB2. However, the instance portion of the name, Instance1, will remain unchanged. In this example, the \\ComputerName\InstanceName would be changed from \\MB1\Instance1 to \\MB2\Instance1.

Before you begin

Before you begin the renaming process, review the following information:

  • When an instance of SQL Server is part of a SQL Server failover cluster, the process of renaming the computer differs from the process of renaming a computer that hosts a stand-alone instance. For information about renaming a computer that hosts a failover cluster instance of SQL Server 2005, see How to: Rename a SQL Server 2005 Virtual Server.
  • SQL Server does not support renaming computers that are involved in replication, except in the case of using log shipping with replication. The secondary computer in log shipping can be renamed if the primary computer is permanently lost. For more information, see Replication and Log Shipping.
  • When you rename a computer that is configured to use Reporting Services, Reporting Services might not be available after the computer name change. For more information, see Renaming a Report Server Computer.
  • When renaming a computer that is configured to use database mirroring, you must turn off database mirroring before the renaming operation, and then re-establish database mirroring with the new computer name. Metadata for database mirroring will not be updated automatically to reflect the new computer name.
  • After the computer renaming operation, users who connect to SQL Server through a Windows group that uses a hard-coded reference to the computer name will not be able to connect to SQL Server if the Windows group specifies the old computer name. To ensure that such Windows groups have SQL Server connectivity following the renaming operation, update the Windows group to specify the new computer name.

You can connect to SQL Server using the new computer name after you have restarted SQL Server. However, to ensure that @@servername returns the updated name of the local server instance, you should manually run one of the following procedures, depending on whether you are updating a default or named instance.

To rename a computer that hosts a stand-alone instance of SQL Server 2005

  • For a renamed default instance, run the following procedures:

    sp_dropserver <old_name>
    GO
    sp_addserver <new_name>, local
    GO
    

    Restart the SQL Server instance.

  • For a renamed named instance, run the following procedures:

    sp_dropserver <old_name\instancename>
    GO
    sp_addserver <new_name\instancename>, local
    GO
    

    Restart the SQL Server instance.

Verify the renaming Operation

After a computer has been renamed, any connections that used the old computer name must connect using the new name.

To verify that the renaming operation has completed successfully

  • Select information from either @@servername or sys.servers. The @@servername function will return the new name, and the sys.servers table will show the new name.

Issues with Remote Logins

If the computer has any remote logins, running sp_dropserver may generate an error similar to this:

Server: Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 44
There are still remote logins for the server 'SERVER1'.

To resolve the error, you must drop remote logins for this server.

To drop remote logins

  • For a default instance, run the following procedure:

    sp_dropremotelogin old_name
    GO
    
  • For a named instance, run the following procedure:

    sp_dropremotelogin old_name\instancename
    GO
    

See Also

Tasks

How to: Rename a SQL Server 2005 Virtual Server

Help and Information

Getting SQL Server 2005 Assistance