Change MS SQL Server 2005 Windows Server Name

Yesterday I change my development computer name at work when adding to the corporate domain, then I had to update the new SQL Server 2005 default instance to SP3 and it does not work, complaining that can’t find ‘oldname’ server name.

I found a solution here thanks to Nick Berardi.

These was his steps (I just add one verification select statement)

— you’ll see your old name here
sp_helpserver
select @@servername
select * from sysservers

— I add the , ‘droplogins’ with the hope that fix te OLDNAME\ASP and so on
— accounts and schemas (doesn’t work, sorry)
sp_dropserver ‘oldname’, ‘droplogins’
sp_addserver ‘newname’,’local’

— then restart services (from console or services.msc
net stop mssqlserver
net start mssqlserver

— finaly check your changes
sp_helpserver
select @@servername
select * from sysservers

Then you have to change the Login accounts (and of course you have to rename some windows local groups)

to change the name of logins in SQL Server: (check here for reference)
ALTER LOGIN [OLDNAME\ASPNET] WITH NAME = [NEWNAME\ASPNET] and so on.

And then I found that I had to change the DEFAULT SCHEMAS also, so if you want to finish that way you can try that and find the registry keys, and so.

I my case I drop that solution and uninstall / install a fresh new database (’cause I had the advantage that my SQL Server was new anyway)

References
http://www.coderjournal.com/2008/02/how-to-change-instance-name-of-sql-server/
http://msdn.microsoft.com/en-us/ms143799.aspx
http://msdn.microsoft.com/en-us/ms143799.aspx

Advertisements

One thought on “Change MS SQL Server 2005 Windows Server Name

  1. Pingback: Cómo cambiar el nombre de un servidor de windows que ya tiene instalado MS SQL Server 2005 « Ernesto Ocampo Blog en Español

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