Monday 9 February 2009

Change name of server, after install SQL Server 2005

If you change the name of a server / computer, after installing SQL server 2005 (it happened to me also for SQL 7.0 server and SQL 2000 server), some of the programs that have access to the database, will have problems, because of the default instance was using the old name.
A long time ago, I had to backup my databases, uninstall SQL server, change the server name, reinstall SQL server, and restore the databases ....
But the solution is very simplistic: change the name of the server, and after restarting it, launch the SQL Management Studio (Enterprise Manager or Query Analyzer if using SQL server 2000), then execute the following queries:

1) select @@servername
It will show you the actual server name used by SQL server

2) sp_dropserver OLDNAME
GO
It will erase this parameter

3) sp_addserver NEWNAME, local
GO
It will configure the SQL server parameter with the new name

4) Restart SQL server services

5) select @@servername
It will show you the actual NEW server name used by SQL server. Try at least twice.

You're all done.
It works for the default instance.
If you need to read further, go to http://msdn.microsoft.com/en-us/library/ms143799.aspx

No comments:

Post a Comment