[ create a new paste ] login | about

Link: http://codepad.org/50PzZw7t    [ raw code | fork ]

Plain Text, pasted on Sep 5:
/*
	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



Create a new paste based on this one


Comments: