How to Change Server Name in SQL server


How to use ‘SP_addserver’ and ‘SP_dropserver’

We often use to change server name for SQL server 2000, 2005 or 2008 instance when we migrate sql instance from one place to another or while changing the name of the computer running the SQL Server.

We have to perform following steps to ensure that SQL Server also uses the new updated servername.

Please execute following select query to check whether the SQL Server has the correct name.


SELECT @@SERVERNAME AS Old_Name,
       Serverproperty('Servername') AS Actual_Name

 

@@Servername gives the computer name that has been set/registered with SQL Server and
Serverproperty(‘Servername’) provides the newly updated name from windows.

If @@servername, Serverproperty(‘Servername’) results are different then
one should update the new name on the SQL Server. Please follow these steps given below…

Step 1 :- First run below to drop the current server name

EXEC Sp_dropserver 'oldservername'

and then run this to add new server name

EXEC Sp_addserver 'newservername', 'local'

 


Step 2 :- Update sysjobs (Applies only to SQL 2000).


USE msdb

UPDATE sysjobs SET originating_server = SERVERPROPERTY('Servername')

Step 3 :- Restart SQL Service

Restart SQL Services for the changes to take effect.

Even if one doesnt update the Servername on SQL Server, SQL Server will still function,but one can face a few issues like the error

‘Error 14274: Cannot delete a job that originated from an MSX server’

The above error appears, when one attempts to change the SQL jobs properties like enabled/disabled and if the Servername is not correctly updated.

Linked server configurations may not work correctly. So in general, if the servername is changed its better to update it on SQL Server using the above mentioned steps.

Thanks

Amit

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: