Steps to change the server name for a SQL Server machine

By:   |   Updated: 2011-11-25   |   Comments (16)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | > SQL Server Configurations


Problem

In this tip we look at the steps within SQL Server you need to follow if you change the physical server name for a standalone SQL Server.

Solution

As you may know, the internal SQL Server name is based on the physical server name, but changing the physical server name does not update the metadata within SQL Server.

When connecting to SQL Server, we use the physical server name for the default instance and physical server name\INSTANCE Name for a named instance. If you change the physical server name, there is no need to re-install SQL Server to reset the physical server name. You can follow the below steps to update the SQL Server system metadata which stores the physical server name in sys.servers.

The below solution works for default and named SQL instances on a standalone machine (non-clustered). Also, you need to follow the steps for each SQL Server instance on the machine.

Pre Update Steps

Check for Remote Logins

  • If remote logins exist you have to drop them first, if you do not drop the remote logins you will get the below error when executing the sp_dropserver stored procedure. 
Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 56
There are still remote logins or linked logins for the server 'sqldbpool'.
  • You can run this query to get a list of remote logins that exist on the server.
-- Query to check remote login
select 
srl.remote_name as RemoteLoginName, 
sss.srvname
from sys.remote_logins srl join sys.sysservers sss on srl.server_id = sss.srvid
-- Query to remove the remote login
--Default Instance
sp_dropremotelogin old_physical_server_name
GO
--Named Instance
sp_dropremotelogin 'old_physical_server_name\instancename'
GO

Check for Replication

  • SQL Server does not support renaming computers that are involved in replication. If the SQL Server is involved in replication you have to remove it first. If you do not remove replication, you will get the below error when updating SQL Server System metadata. 
Msg 15185, Level 16, State 1, Procedure sp_dropremotelogin, Line 68
There is no remote user '(null)' mapped to local user '(null)' from the remote 
server 'sqldbpool'.
  • You can run this query to see if replication is in place. If so, you could script out the settings first so you can recreate afterwards and then remove replication.
-- Query to check if the SQL Instance databases are involved in replication
select name,is_published,is_subscribed,is_distributor from sys.databases
where is_published = 1 or is_subscribed =1 or is_distributor = 1
GO

Check for Reporting Services

  • If SQL Server Reporting Service is installed and configured, you have to first stop the Reporting Services service.
sql server configuration manager

Check for Database Mirroring

  • If database mirroring is enabled you have to remove mirroring and then re-establish mirroring once you have made the system metadata update. 
--Query to find out the databases involved in DB mirroring
select database_id, db_name(database_id)
from sys.database_mirroring where mirroring_guid is not null
--Query to turn off the database mirroring
ALTER DATABASE SET PARTNER OFF

Steps to rename SQL Server

  • Execute the below commands for each instance on the machine.
-- for a Default Instance
sp_dropserver <OLD_PHYSICAL_SERVER_NAME>
GO
sp_addserver <NEW_NAME>, local
GO

--for a Named Instance
sp_dropserver <'old_physical_server_name\instancename'>
GO
sp_addserver <'new_name\instancename'>, local
GO
  • Restart the SQL Server services.
  • Execute the below query in each instance to verify the updated system metadata.
SELECT @@SERVERNAME

Post Update Steps

  • Add the remote logins using the sp_addremotelogin command.
sp_addremotelogin [ @remoteserver = ] 'remoteserver' 
     [ , [ @loginame = ] 'login' ] 
          [ , [ @remotename = ] 'remote_name' ]
  • Reconfigure Replication if this was setup.
  • Reconfigure Database Mirroring if this was setup.
  • Reconfigure Reporting Services if this was setup and connect to the new server name as shown below.
reporting services configuration manager
  • If you also changed the Report Server computer name, you will have to edit the RSReportServer.config file and update the URLRoot setting. Do to this, open the RSWebApplication.config file and modify the ReportServerUrl setting to reflect the new server name.
Next Steps
  • Change application connection strings pointing to the server
  • Change linked servers pointing to the server


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jugal Shah Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2011-11-25

Comments For This Article




Friday, September 6, 2019 - 1:42:40 AM - Bhima Sekhar Back To Top (82263)

Sir,

I am having hosting in that I placed my project details, but in that I want to change server name from other server to my own server how can I rectify pls reply.

Sunday, November 11, 2018 - 8:19:21 AM - Prathap Back To Top (78207)

Hi ,

Can someone please help me to change machine name for my sql instance. Actually i installed sql server with the name ÄG on server3 host....but somehow i can see machinename is different for my instance. Please check and let me know for the same. Thanks.

 

host_name()    ServerName\InstanceName    ServerName    Windows_Name    NetBIOS_Name    InstanceName    IsClustered
SERVER3    SERVER3\AG    MS-CLUSTER2\AG    MS-CLUSTER2    MS-CLUSTER2    AG    0

Friday, June 15, 2018 - 10:15:00 AM - Vijay Back To Top (76230)

 Hi,

I have issue with SSRS Report Server Configuration Manager in the server.

I changed server name. Now report server connection not working. I tried to change server name in database tab but its seems disable.

http://prntscr.com/jvcoej

Can you please suggest me steps to update server name.

 

Tuesday, December 6, 2016 - 9:48:36 AM - dev Back To Top (44907)

What about SSIS rename ?, will it be changed automatically or do we need to change it manually? how to change it?

Tuesday, August 9, 2016 - 1:35:57 PM - John Burnette Back To Top (43089)

 Nice article -- do you know if this works for SQL 2014 and SQL 2016 versions  (Standalone-mode, not clustered)?

I seem to remember that this didn't work with the initial releases of SQL 2014 but I can't recall the exact error message...there was nothing else running -- just a blank, generic, simple install of SQL 2014 and the sp_dropserver and sp_addserver didn't work as intended.  I believe MSFT may be deprecating one or both of these in future releases.

 

Friday, November 6, 2015 - 5:14:33 PM - Scott Back To Top (39039)

If you have a maintenance plan and the server name changes, the maintenance plan connection string for "(Local server connection)" will be wrong and you can't change it through the Maintenance Plan editor.  For instance, you clone a virtual machine with a SQL Server that has a backup maint plan, and put the clone on the same network with a new name.  The maint plan on the clone will continue to run backups on the original.

You can delete the maintenance plan and recreate it.  Or you could do it in SQL with the queries below.  It might be risky, but the worst that can happen is that you'll have to delete and recreate it.

-- Get the package definition as XML
SELECT FolderName = f.foldername, PackageName = p.name, PackageId = p.id, PackageData = CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) 
FROM msdb.dbo.sysssispackages p
INNER JOIN msdb.dbo.sysssispackagefolders f ON f.folderid = p.folderid
WHERE f.foldername = 'Maintenance Plans'

-- Click on the PackageData grid cell to open it in an XML window.
-- Search and replace "OldServerName" with "NewServerName". It could be in connection strings, backup paths, task names and descriptions, etc.
-- Copy the result and paste it into the @packageXML definition below.  Also copy the PackageID value into the WHERE clause.
DECLARE @packageXML XML;
SET @packageXML = 'Edit the packageXML data manually, then copy and paste it here';
UPDATE msdb.dbo.sysssispackages SET packagedata = CAST(@packageXML AS VARBINARY(MAX))
WHERE id = 'package GUID'

Thursday, June 25, 2015 - 1:44:03 PM - Allen Back To Top (38039)

Hi - How can I change name on SQL Server Configuration Manager as well?

Thursday, April 9, 2015 - 1:54:33 PM - eric81 Back To Top (36881)

It looks like sys.databases is_subscribed column isn't used looks like it will always return a "0" output.  Just an FYI.

 

Wednesday, January 14, 2015 - 7:30:21 AM - Shan Back To Top (35935)

Hi,

Actually we got one requirement from client i.e uninstall old sql server instance (suppose ABCD\XYZ) and new named instance install on ABCD server.

so for above requirement what i did....

sp_dropserver 'ABCD\XYZ'

then sp_addserver 'ABCD\PQR',local

then i fired below command

select @@servername it is showing new server instance name like ABCD\PQR

i am able to coonect with new server and every thing is fine. in my server there is no remote logins,replication nothing....

But what is the problem in configuration manager it is showing old name only i.e Sql server(XYZ)

how i am changing that one........

please help me if you have any solution......

Thursday, September 18, 2014 - 2:46:43 PM - ann Back To Top (34612)

Question about rename reporting server, after rename server name, on the scale out deployment screen in Reporting services configuration Manager, will the servername there changed automatically?

We are using standard edition, and we only need one server listed there.

Thanks

Monday, November 25, 2013 - 10:07:50 AM - Pratap Back To Top (27594)

The above queries will only help if you have your server name changed and not the instance name.That's needed when the server name has changed (so ServerOld\SQL01 to ServerNew\SQL01). You cannot in any way change the name of a named instance (short of uninstalling the whole thing and reinstalling with a new name).

I kind of got into same situation and have to uninstall and re-install for new named instance  in SQL 2012.

Monday, August 12, 2013 - 6:15:17 PM - Jay Back To Top (26262)

I Just did exaclty same with named instance, to change to diffrent but the instance name changed internally , when i execture select@@serverproperty and servername it got the changed name but , the original connection still using the old name.

Thursday, March 7, 2013 - 6:27:44 AM - prathap chowdary Back To Top (22627)

Hi,

Actually we got one requirement from client i.e uninstall old sql server instance (suppose ABCD\XYZ) and new named instance install on ABCD server.

so for above requirement what i did....

sp_dropserver 'ABCD\XYZ'

then sp_addserver 'ABCD\PQR',local

then i fired below command

select @@servername it is showing new server instance name like ABCD\PQR

i am able to coonect with new server and every thing is fine. in my server there is no remote logins,replication nothing....

But what is the problem in configuration manager it is showing old name only i.e Sql server(XYZ)

how i am changing that one........

please help me if you have any solution......

 

Regards,

Prathap.

Tuesday, May 22, 2012 - 7:40:19 PM - sqlq Back To Top (17595)

hello,

I see in  my rsreportserver.config the <UrlRoot>  </UrlRoot> is empty, the same as <ReportServerUrl>
  </ReportServerUrl>.

For my case , we have both reporting service and database engine that hosts report databases on the same server.

We changed the physical computer name, then I changed the sql server name by using sp_dropserver and addserver.

The step I am missing is next step after above changes. If you can tell me next step, that would be helpful.

I see empty string <UrlRoot> , and reportserverURl

Friday, November 25, 2011 - 10:04:10 AM - ihar Back To Top (15206)

Thanks a lot!!! Easy and useful.

Friday, November 25, 2011 - 1:36:15 AM - chintan parekh Back To Top (15203)

Nice Article, really helpful. thanx..:)














get free sql tips
agree to terms