• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1640
  • Last Modified:

change sql server 2000 instance name

Expert,

I want to change sql server name and sql server instance name (
from name instance to local).
sp_dropserver 'oldservername'
GO
sp_addserver 'newname','local'
GO

Stop and restart the SQL Server service for the changes to take effect.
You can verify it afterward with
SELECT @@SERVERNAME
it is changed sql server name and name instance but when I go to EM, delete register of sql server and reregister sql server new instance, it got failed. and
EM couldn't change new instance name.
Can you help?

0
yrcdba7
Asked:
yrcdba7
  • 7
  • 4
1 Solution
 
brandonvmooreCommented:
I'm not really familiar with that function, but I don't think it does what you are wanting to do.  The only way I know of to really change the instance name is to run setup and install a new instance, and then you can remove your old instance.

I read somewhere that sp_addserver is the same thing as sp_addlinkedserver and that while it's still available in newer versions of SQL, it's only there for backwards compatibility.  I don't know if this is actually true or not, but sp_addlinked server is certainly not the same thing as what you're trying to do.
0
 
yrcdba7Author Commented:
so you mean I need to install name instance again. is it effect sp3a and sp4?
should I reinstall sp3a or sp4?  I want this server exactly match prodution server.
0
 
brandonvmooreCommented:
You're not installing SQL Server again, but run the same setup program you ran to install SQL Server and you will have the option to add a new instance.  Sp level will not be affected.

btw, I just saw an article where someone was talking about renaming using the method you were trying to use.  From his post it made it sound like it worked just fine, however I think his post was ambiguous and that he was really creating some kind of alias for a networked server so that some existing software would not have to be modified.  All other articles I've read, including ones from microsoft, say you can't rename an instance.

You'll obviously want to back up all your databases from the first instance and then restore them to the new one before removing your old instance.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
brandonvmooreCommented:
Ok, hold up.  I'm sorry it looks like I'm misinforming you.  Apparently in SQL 2k you 'can' rename instance, but in the newer versions (which I'm more familiar with) you can't.

See:
http://msdn.microsoft.com/en-us/library/aa197071(SQL.80).aspx

If I find out anything else I'll let you know.
0
 
brandonvmooreCommented:
OK, maybe I didn't misinform you after all.  I pulled up that article by clicking on "can you change an instance name after it's been installed?" on a microsoft page.  But it looks like that procedure is really just for updating SQL2K whenver you have changed your computer name.  It doesn't look like it actually changes instance names in spite of the misleading question I read.
0
 
yrcdba7Author Commented:
I install MS sql server 2k again for default instance, then I check sp# it is NULL.

I delete old instance and registered local instance, but I could not open Query Analyze windows.  
0
 
yrcdba7Author Commented:
I remove sql server 2k for the default intance, I reinstall again. I need install sp3a and sp4.  can I only install sp4? if I can not, I have to install sp3a, how can I set up sp3a and sp4? I tried to follow MS online but after set up I run command
select serverproperty('productionlevel'), I only can see sp4.
0
 
brandonvmooreCommented:
I'm wondering if we're on the same page here?  Tell me what the old instance name was and what you wanted the new instance name to be and that will let me know if we're on the same page and how to respond from here.

To answer your question: SP4 is cumulative so you do not need to install any prior SP's.  If you have SP4 and want to go back to a prior SP, I personally don't know how to do that short of removing and reinstalling SQL Server.  However, there's really no reason to revert to a prior SP as the SP's fix bugs and such, they don't change existing functionality.
0
 
yrcdba7Author Commented:
You mean I dont't need to install sp's if I have installed in my machine. but after I install sql server 2k in defualt instance, I check command:
select serverproperty('productionlevel', it is NULL.  That mean it is no sp's with
default instance.

My name instance is sql2kA, my default instance is local.

0
 
brandonvmooreCommented:
Good Lord, am I the only person on this site that can speak in complete sentences?  I seriously get tired of trying to help people like you who seem to be unable to speak coherently.  Start proof reading your messages before you submit them for goodness sake.

"You mean I dont't need to install sp's if I have ??? installed in my machine"
If you have WHAT installed on your machine?  I could make an educated assumption as to what you mean there, but I really don't like doing that because the fact that you couldn't write a complete sentence is an indication that my 'educated' guess could easily be wrong.

The sp's apply to ALL instances on your machine.  If one instance is up to sp4 then all of them are.  In a previouis comment you stated:

"I tried to follow MS online but after set up I run command select serverproperty('productionlevel')..."

It's not 'productionslevel', it's 'productlevel'.  If no sp's were available it wouldn't be null, it would say RTM.

Your default instance is not 'local'.  Your machine is local.  The default instance is always unnamed... so you when you specify 'local' it is simply saying to look for the unnamed (default) instance on the machine the connection is being made from.  When you see "server\instancename", the name of your computer is what the 'server' part is.  You can type either "." or "(local)" instead of your machine name because it means the same thing.

Originally you said you wanted to change the server name and the instance name.  Did you change the name of your computer?  If you changed the name of your computer then it is necessary to update the server name using the method you were originall using.  You just mentioned that you have both a named instance (sqk2kA) and a default instance, so if your goal is to use the default instance then you will need to back up whatever you have on the sql2kA instance and restore it to the default instance.
0
 
brandonvmooreCommented:
Also, if tried to use the sp_dropserver and sp_addserver to change the 'instance' name (which is not what these are for, they are for updating the 'server' name in sql), then you may need to drop whatever 'instance' name you added, and then add your computer name as the server.  I don't know if this will be necessary or not, but if you have trouble getting a connection then definitely do this.

And just FYI: in SQL2005 and later versions you don't have to use these procedures when you change your computer name because it is automatically taken care of.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now