Monday, April 30, 2007

Rename SQL installation

Am taking no credit for this.
Script from a colleague, it lets you rename a sql server, i.e. tell SQL about the fact that the server has been renamed!

DECLARE  @machine  SYSNAME,
        @instance SYSNAME

SELECT @instance = CASE
                  WHEN CHARINDEX('\',@@SERVERNAME) = 0 THEN ''
                  ELSE SUBSTRING(@@SERVERNAME,CHARINDEX('\',@@SERVERNAME),
                                   (LEN(@@SERVERNAME) + 1) - CHARINDEX('\',@@SERVERNAME))
                  END

SELECT @machine = CONVERT(NVARCHAR(100),SERVERPROPERTY('MACHINENAME')) + @instance;


EXEC SP_DROPSERVER
 @@SERVERNAME;

EXEC SP_ADDSERVER
 @machine ,'local'

NB : Remember to restart the SQL Server service after running the script.

No comments: