/*
This script can be used to automatically rename a SQL Server
based on the current computer host name
Run this script after:
Computer Name Changed
Virtual Machine Cloned
*/
DECLARE
@newHostName SYSNAME = HOST_NAME()
, @oldHostName SYSNAME
, @newInstanceName SYSNAME
, @oldInstanceName SYSNAME = @@SERVERNAME
SELECT @oldHostName = CASE
WHEN CHARINDEX('\', @oldInstanceName) = 0 THEN @oldInstanceName
ELSE LEFT(@oldInstanceName, CHARINDEX('\', @oldInstanceName) - 1)
END
IF ( @oldHostName = @newHostName )
BEGIN
PRINT('SQL Server is already named properly')
END
ELSE
BEGIN
SELECT @newInstanceName = REPLACE(@oldInstanceName, @oldHostName, @newHostName)
-- Rename SQL Server
EXEC('EXEC sp_dropserver N''' + @oldInstanceName + '''')
EXEC('EXEC sp_addserver N''' + @newInstanceName + ''', local')
PRINT('SQL Server renamed from ''' + @oldInstanceName + ''' to ''' + @newInstanceName + ''', restart SQL Server services to complete the changes')
END