Automating the rename of a SQL Server

Question

We're in the process of replacing our SQL Server and have decided that renaming the server itself is going to be much easier than changing everything else to point to a new name. We found instructions on changing the SQL Server instance name to match the computer name which look like this:

sp_dropserver 'OLDSERVER'
sp_addserver 'NEWSERVER', local

Though SQL Enterprise Manager doesn't seem to like those together. I had to change it to the following to make it work together:

sp_dropserver 'OLDSERVER'; GO
sp_addserver 'NEWSERVER', 'local'; GO

Which is not bad, but I would prefer things to be more automated. Since @@ServerName returns the name of the instance, I figured out how to automate the first line:

sp_dropserver @@ServerName; GO

I also learned that SERVERPROPERTY('ServerName') is supposed to return the computer name, so I thought I could maybe use that to automate the second part, but this didn't work:

sp_addserver SERVERPROPERTY('ServerName'), 'local'; GO

I tried setting a variable, which I need to do anyway to update the SQL Agent jobs, but that didn't work either:

DECLARE @srv sysname;
SET @srv = CAST(SERVERPROPERTY('ServerName') AS sysname);
sp_addserver @srv, 'local'; GO

Incorrect syntax near 'sp_addserver'.

I would very much like to not have to hardcode the new server name into the script, to make this more easily reusable. Anybody got any ideas?

Answer 1

Here's a script to do what you want:

DECLARE @var1 nvarchar(50)
DECLARE @var2 nvarchar(50)
SET @var1 = convert(nvarchar(50),@@SERVERNAME)
SET @var2 = convert(nvarchar(50),SERVERPROPERTY('MachineName'))
EXEC sp_dropserver @var1
EXEC sp_addserver @var2, 'local'
GO

Credits: http://www.myitforum.com/articles/5/view.asp?id=4983

Answer 2

With help from magma and Sankar Reddy, here is the finished script:

DECLARE @var1 nvarchar(50)
DECLARE @var2 nvarchar(50)
SET @var1 = convert(nvarchar(50),@@SERVERNAME)
SET @var2 = convert(nvarchar(50),SERVERPROPERTY('MachineName'))
EXEC sp_dropserver @var1
EXEC sp_addserver @var2, 'local'
UPDATE msdb.dbo.sysjobs SET originating_server = @var2
GO

The article that magma found had one small error in it, which was that it was trying to drop the new name and add the old name instead of dropping the old name and adding the new name. I also added an update query to fix the SQL agent jobs. Note that this only works correctly against the master server in a multi-server environment. My environment is a single-server environment, so it works for my situation.

Answer 3

Declare @OldName varchar(50),
        @NewName Varchar(50)

Select @OldName = CONVERT(VARCHAR(50),@@SERVERNAME) ;
--SELECT @OldName [OLDNAME]
Select @NewName =  CONVERT(VARCHAR(50),SERVERPROPERTY('ServerName'));
--SELECT @NewName [NEWNAME]

EXEC sp_dropserver @OldName;
EXEC sp_addserver @server = '@NewName',@local='local';
GO