Script to Rename an SQL Server Instance

Let’s just say either:

• You have SQL Server installed on an VM that you’re going to clone often
• You moved a SQL Server intance to a new server
• You renamed the server that SQL Server is running on

In any of those scenarios, you’ll have some hardships, such as:

• Some SQL Server Agent jobs won’t run or you won’t be able to modify them
• You might not be able to install 3rd-party software properly, (I ran into this problem with Quest Software’s LiteSpeed for SQL Server recently)
• Applications that verify @@SERVERNAME may not work. Typically, this is done by vendors for licensing concerns.

Either way, I think you’ll find this script very useful, because you can use it to quickly rename an SQL Server 2005 or 2008 instance (it even takes named instances into account!). It assumes the name of the physical host that the instance is running on, so for now, clusters are not supported. When I figure out how to accomodate clusters, I’ll edit this post.

With a little bit of batch scripting, you can even run the script using sqlcmd and then restart the SQL Server instance so the changes will take effect.

Please note one thing: This script will not change the default instance name that SSMS attempts to log into. However, if you know how to change that, please leave a comment and I’ll add it to this post.

SET NOCOUNT ON

DECLARE @InstanceName sysname

SELECT @InstanceName = ”

DECLARE

@MachineName sysname,

@CurrentServerName sysname,

@ComputerNamePhysicalNetBIOS sysname,

@NewServerName sysname

SELECT

@CurrentServerName = @@ServerName

, @MachineName = CONVERT(sysname, SERVERPROPERTY(‘MachineName’))

, @ComputerNamePhysicalNetBIOS = CONVERT(sysname, SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’))

, @NewServerName = CONVERT(sysname, SERVERPROPERTY(‘ServerName’))

IF CHARINDEX(‘\’, @CurrentServerName) != 0 BEGIN

SELECT @InstanceName = RIGHT(@CurrentServerName, LEN(@CurrentServerName) - CHARINDEX(‘\’, @CurrentServerName)+1)

SELECT @NewServerName = @MachineName + @InstanceName

END

–SELECT

– @CurrentServerName [@CurrentServerName]

– , @MachineName [@MachineName]

– , @ComputerNamePhysicalNetBIOS [@ComputerNamePhysicalNetBIOS]

– , @InstanceName [@InstanceName]

– , @NewServerName [@NewServerName]

IF @CurrentServerName != @NewServerName BEGIN

PRINT ‘Computer Name has been changed since last time SQL Server was reconfigured. Changing the config now…’

PRINT ‘Note, this only works for the default instance of a stand-alone server. Do not use this script on a cluster…’

PRINT ‘You will need to restart this SQL Server instance for the changes to take effect.’

IF EXISTS(SELECT * FROM sys.sysservers WHERE srvname = @CurrentServerName) BEGIN;

PRINT ‘DROPPING [' + @CurrentServerName + ']!!!’

EXEC master.dbo.sp_dropserver @CurrentServerName

PRINT ‘[' + @CurrentServerName + '] DROPPED…’

END;

IF NOT EXISTS(SELECT * FROM sys.sysservers WHERE srvname = @NewServerName) BEGIN;

PRINT ‘ADDING [' + @NewServerName + ']!!!’

EXEC master.dbo.sp_addserver @NewServerName, local

PRINT ‘[' + @NewServerName + '] ADDED…’

END;

END ELSE BEGIN

PRINT ‘SQL Server ServerName does not need to be corrected.’

END