How To Rename A SQL Server

Like some other posts recently, this one is the result of a conversation with a colleague at my new company, where I found they were unaware of something I took for granted; the ability to easily rename a comuter which has a stand alone instance of SQL Server installed.

I do this fairly often as I use quite a lot of Virtual Machines for testing different configurations, but I don’t want to have to build a new one every time, so I make copies of a base VM and rename it each time I want a new one (there is often more than one running at any one time and I want to avoid conflicts).

As well as renaming the machine inside the VM, I also rename the SQL Server to make sure it matches the name of the machine; I’ve had problems before with Microsoft Dynamics GP when I’ve not done so.

Doing this is actually very simple. After renaming and restaring the computer, I always confirm the current name of the SQL Server;

SELECT @@SERVERNAME
GO

The script to rename SQL Server is (assuming the old machine name, returned by the previous script, was DB1 and the new one SQL1);

EXEC sp_dropserver 'DB1'
GO

EXEC sp_addserver 'SQL1', LOCAL
GO

Restart the SQL server to complete the renaming operation.

This script will work with named instances of SQL as well as the default one as shown above. If it had been an instance of SQL named db1\SQL2008R2 the script to rename it would be;

EXEC sp_dropserver 'DB1\SQL2008R2'
GO

EXEC sp_addserver 'SQL1\SQL2008R2', LOCAL
GO