Changing the name of a SQL Server Host machine

Overview
If you change the name of a machine with SQL Server installed, you need to do a few things in SQL Server after the host name change.
This blog applies to SQL Server 7.0, 2000, 2005 and 2008.

Note: If the SQL Server is installed in a cluster, you should reinstall.

Step 1 (all versions)
After starting SQL Server, you need to take care of the sysservers table in the master database. SQL Server stores the local machine name here and this will not match if you have renamed the machine in Windows. This is also easy to fix:

EXEC sp_dropserver ''
GO
EXEC sp_addserver '', 'local'
GO

You need to replace above with the old machine name and with the new machine name. If you have instance name, then the server names will be .
Get old name of server by executing-

EXEC sp_helpserver
GO

OR
SELECT * FROM sysservers

Step 3 (7.0 and 2000)
In this last step, you need to handle the sysjobs table in the msdb database. There is one row in sysjobs for each SQL Server agent job. In the column originating_server, you find the name of the server where the job was created. This is to support master and target server (MSX). If a job is created on a master server, you cannot modify the job definition on the targer server; all modifications has to be done on the master server.

So, if you changed the machine name, SQL Server will think that the job originated on a master server, and you will not be able to do anything with that job definition. You can handle this in two ways:

1. Rename the machine back to the old name, script the job definitions, delete the jobs, rename the machine to the new name again and use the script to re-create the jobs. See Error 14274 Occurs When You Update a SQL Agent Job After Renaming Windows Server for more information.

2. I find it easier to modify the sysjobs table directly. Note that this isn't supported and if you don't feel confident with doing below, don't; use above steps instead.

DECLARE @srv sysname
SET @srv = CAST(SERVERPROPERTY('ServerName') AS sysname)
UPDATE sysjobs SET originating_server = @srv

If this is a target server (you have jobs sent from a master server), you have to exclude them, so you don't convert those jobs into local jobs:

WHERE originating_server = ''

You need to replace above with the old machine name.