Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
1  
Can you add EXEC before sp_addserver and see if it helps? –  Sankar Reddy May 25 '11 at 22:29
    
That made it work. :) –  Scott May 26 '11 at 14:50

2 Answers 2

up vote 4 down vote accepted

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

share|improve this answer
1  
Almost, but @var1 should be dropped and @var2 added. @@ServerName returns the name of the SQL Server instance, while SERVERPROPERTY('MachineName') returns the name of the computer. –  Scott May 26 '11 at 14:51
    
@scott my bad; fixed, thank you. –  magma May 26 '11 at 17:51

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.