Click to Rate and Give Feedback
MSDN
MSDN Library
SQL Server
SQL Server 2008
Getting Started
 How to: Rename a Computer that Host...
Other versions are also available for the following:
SQL Server 2008 Books Online (August 2008)
How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server

When you change the name of the computer that is running SQL Server, the new name is recognized during SQL Server startup. You do not have to run Setup again to reset the computer name. Instead, use thethe following steps to update system metadata that is stored in sys.servers and reported by the system function @@SERVERNAME. Update system metadata to reflect computer name changes for remote connections and applications that use @@SERVERNAME, or that query the server name from sys.servers.

The following steps cannot be used to rename an instance of SQL Server. They 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 part 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 computer renaming process differs from a computer that hosts a stand-alone instance.
  • SQL Server does not support renaming computers that are involved in replication, except when you use 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 you rename a computer that is configured to use database mirroring, you must turn off database mirroring before the renaming operation. 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. Use the following steps to update system metadata.
  • Users who connect to SQL Server through a Windows group that uses a hard-coded reference to the computer name might not be able to connect to SQL Server. This can occur after the rename 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 by using the new computer name after you have restarted SQL Server. To ensure that @@SERVERNAME returns the updated name of the local server instance, you should manually run the following procedure that applies to your scenario. The procedure you use depends on whether you are updating a computer that hosts a default or named instance of SQL Server.

  • For a renamed computer that hosts a default instance of SQL Server, run the following procedures:

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

    Restart the instance of SQL Server.

  • For a renamed computer that hosts a named instance of SQL Server, run the following procedures:

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

    Restart the instance of SQL Server.

  • For a renamed computer that hosts a default instance of SQL Server, run the following procedures:

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

    Restart the instance of SQL Server.

  • For a renamed computer that hosts a named instance of SQL Server, run the following procedures:

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

    Restart the instance of SQL Server.

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

  • 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. The following example shows the use of @@SERVERNAME.

    SELECT @@SERVERNAME AS 'Server Name'

Remote Logins - If the computer has any remote logins, running sp_dropserver might generate an error similar to the following:

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.

  • 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
Tags What's this?: Add a tag
Community Content   What is Community Content?
Add new content RSS  Annotations
Maintenance Plans      laridae ... moody31415   |   Edit   |  

Maintenance plans don't get their connections changed to the new server name and so they may break. After a rename you may find that you cannot delete or rename the existing maintenance plans, so either delete them before renaming the server and recreate them afterwards or run the following script to fix them:

Note: This mostly worked for me, except I had to use the sysssispackages table instead of sysdtspackages90 table.

use msdb
DECLARE @oldservername as varchar(max)
SET @oldservername='<server name>\<instance name>'

-- set the new server name to the current server name
declare @newservername as varchar(max)
set @newservername=@@servername

declare @xml as varchar(max)
declare @packagedata as varbinary(max)
-- get all the plans that have the old server name in their connection string
DECLARE PlansToFix Cursor
FOR
SELECT id
FROM sysdtspackages90
WHERE (CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX)) LIKE '%server=''' + @oldservername + '%')

OPEN PlansToFix


declare @planid uniqueidentifier
fetch next from PlansToFix into @planid
while (@@fetch_status<>-1)  -- for each plan

begin
if (@@fetch_status<>-2)
begin
select @xml=cast(cast(packagedata as varbinary(max)) as varchar(max)) from sysdtspackages90 where id= @planid -- get the plan's xml converted to an xml string

declare @planname varchar(max)
select @planname=[name] from sysdtspackages90 where id= @planid -- get the plan name
print 'Changing ' + @planname + ' server from ' + @oldservername + ' to ' + @newservername -- print out what change is happening

set @xml=replace(@xml,'server=''' + @oldservername + '''','server=''' + @newservername +'''') -- replace the old server name with the new server name in the connection string
select @packagedata=cast(@xml as varbinary(max)) -- convert the xml back to binary
UPDATE sysdtspackages90 SET packagedata = @packagedata WHERE (id= @planid) -- update the plan

end
fetch next from PlansToFix into @planid -- get the next plan

end

close PlansToFix
deallocate PlansToFix
  
----- This will also handle the packages that have a tag such as 
----- <DTS:Property DTS:Name="ConnectionString">Data Source=servername;Integrated Security=SSPI;Connect Timeout=30;</DTS:Property>
  
DECLARE @oldservername as varchar(max)
SET @oldservername='<server name>\<instance name>'-- set the new server name to the current server name
declare @newservername as varchar(max)
set @newservername = @@servername
declare @xml as varchar(max)
declare @packagedata as varbinary(max)-- get all the plans that have the old server name in their connection string
DECLARE PlansToFix Cursor FOR
SELECT id
FROM sysdtspackages90
WHERE (CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX)) LIKE '%Data Source=' + @oldservername + '%')

OPEN PlansToFix
declare @planid uniqueidentifier
fetch next from PlansToFix into @planid
while (@@fetch_status<>-1) -- for each plan
begin
if (@@fetch_status<>-2)
begin
select @xml=cast(cast(packagedata as varbinary(max)) as varchar(max))
from sysdtspackages90 where id= @planid -- get the plan's xml converted to an xml string
declare @planname varchar(max)select @planname=[name] from sysdtspackages90 where id= @planid -- get the plan name
print 'Changing ' + @planname + ' server from ' + @oldservername + ' to ' + @newservername -- print out what change is happening
set @xml=replace(@xml,'Data Source=' + @oldservername,'Data Source=' + @newservername) -- replace the old server name with the new server name in the connection string
select @packagedata=cast(@xml as varbinary(max)) -- convert the xml back to binary
UPDATE sysdtspackages90 SET packagedata = @packagedata WHERE (id= @planid) -- update the plan
end
fetch next from PlansToFix into @planid -- get the next plan
end
close PlansToFix
deallocate PlansToFix
Tags What's this?: Add a tag
Flag as ContentBug
Kudos on the fix above for Maintenance plans after a name change!      Steve Ware   |   Edit   |  
Steve Ware 8/2008
Tags What's this?: Add a tag
Flag as ContentBug
Processing
© 2008 Microsoft Corporation. All rights reserved. Terms of Use  |  Trademarks  |  Privacy Statement
Page view tracker