Renaming a server can be a mystery in SQL Server. The symtoms that SQL Server displays after you rename a server makes it look like you made a bad mistake. There are registry entries and system table records littered throughout your system that reflect the server's name. In this brief article, I'll show you a few tips on how to quickly change your server's name and hopefully save a few headaches along the way.

Renaming a SQL Server 7.0 Machine

SQL Server 7.0 is the most difficult scenario for renaming your server since you must take an additional step that is an act of faith. After you change the server name and reboot, SQL Server will complain about not being able to start. This is usually where you go and call Microsoft Support or post to a newsgroup. Before you do that though, rerun setup. This is the scary part the first time you do this. The setup Installshield will tell you that it is going to upgrade your system. This will change the registry entries. After that, reboot one more time then connect to the new server name in Query Analyzer. Run the following command:

sp_dropserver 'old server name'
sp_addserver 'new server name', 'local'

Finally, delete the entry in Enterprise Manager for the old server name and then you’re done.

Renaming a SQL Server 2000 Machine

SQL Server 2000 is much easier. After you change the server name and reboot SQL Server will automatically detect that you've changed the server name and then "fix itself". All you'll have to do is connect to the new server name in Query Analyzer and run the following command:

sp_dropserver 'old server name' 
sp_addserver 'new server name', 'local'

Delete the entry in Enterprise Manager for the old server name and then you’re golden. You will have to stop and start the SQL Server service to complete the process.