How to change SQL Server Instance Name?
Posted by Jugal Shah on September 3, 2008
First collect the output of the current instance configuration. You can get the instance name stored in the SQL Server metadata.
Make sure you have backup of all the database if you are changing the production server instance name.
sp_helpserver select @@servername
You can change the instance name using below query.
Default Instance
sp_dropserver 'old_name'
go
sp_addserver 'new_name','local'
go
Named Instance
sp_dropserver 'Server Name\old_Instance_name'
go
sp_addserver 'ServerName\New Instance Name','local'
go
Verify sql server instance configuration by running below queries
sp_helpserver select @@servername
Restart the SQL Server Services.
net stop MSSQLServer net start MSSQLServer
sandeep said
can we create two instances in sql server standard edition?
plz explain.
JShah said
Yes we can create two instances in standard edition
online stock trading advice said
Hey, I found your blog while searching on Google your post looks very interesting for me. I will add a backlink and bookmark your site. Keep up the good work!
I’m Out!
Umesh said
Hi I have installed SQL server2008 on my machine with instance name “”UmeshSQL2008. but some components did not get installed. So i have reinstalled the the same with another instance name say “abcSQL2008″. Now as I have Windows7 installed on my machine there is some compatibility issue, so i have downloaded the service pack for SQL server2008 and tried to installed. but i a getting following error:
TITLE: SQL Server Setup failure.
——————————
SQL Server Setup has encountered the following error:
A failure was detected for a previous installation, patch, or repair for instance ‘UMESHSQL2008′ during configuration for features [SQL_Replication_Core_Inst,SQL_Engine_Core_Inst,MPT_AGENT_CORE_CNI,SQL_FullText_Adv,Analysis_Server_Full,RS_Server_Adv,]. In order to apply this patch package (KB968369), you must resolve any issues with the previous operation that failed. View the summary.txt log to determine why the previous operation failed.
Error code 0x84B20001
Please help me out why this is happening. how do i install the service pack for SQL server 2008.
Thanks
2010 in review « SQLDBPOOL.COM said
[...] You All Very Happy and Prosperous New Year 2011Wishing you all Very happy and prosperous new yearHow to change SQL Server Instance Name?Transactions and Locks in SQL ServerDBA Roles and ResponsibilitiesNetwork Troubleshooting Dos [...]
Gbaksh said
thanks a lot for this tip. It helped me a great deal.
chandra said
Hi Jugal,
Are there any best practices on logshipping articles
what is the best way to copy the big log files / backup files in logshipping from primary server to secondary server
Jugal Shah said
Log file shouldn’t be too big, if you have scenario go for compress backup. Again you can change the interval as well.
Charlie said
I changed the name of the instance and the sp_helpserver shows the new name but the dialog to connect to an instance still shows the old name. How do I get this to reset? I stopped and started the server instance.
Jugal Shah said
Can you please check the registry as well? Is it a default instance or named instance?
Charlie said
The registry has several hundred entries that match the instance name. The instance is a named instance. It looks like a lot of work that I do not have time to do. I just changed the name back to the old instance name for now.
binal pandya said
I am trying to install sql server 2005 express,
But the insatallation has been failed with the error message that the instance is already running.
How can i solve this?
can u help me?
Jugal Shah said
Try to install the named instance…
Kamlesh said
Thanks ..
It really work but you must add 1 step to restart the server
thanks again …
Jessica Godoy said
HI,
is possible change the user of any instance?
Thanks in advance.
Jessica Godoy
Jugal Shah said
yes you can change the user of any instance. Can you give me more information so I can guide you.
ami said
i have database in sqlserver2005 in my pc.so now a days my pc is act as server .bt i want to change server …so what i have to do????n is it affect to my programme???
Jugal Shah said
I am not getting your question. If you want to move the DB server, you have to change the connection string in your programming language and have to restore the database to new machine.
Let me know if you need more infromation.
ami said
pls give me steps how to restore or copy database to new m/c???
Jugal Shah said
Hi Ami,
First you have to take the backup of the database from the source server. You can take the backup using
Backup database [DBName] to disk = ‘C:\dbName.bak’
Next step is copy the the file to target server and execute the below command.
Restore database [DBName] From disk = ‘C:\dbName.bak’
Fernando said
Hi Jugal,
I have a scenario in of my production servers to change the default instance in SQL server 2000. Can the same steps be used in SQL 2000 also. If not could you please any suggestions and that would be very helpful.
Thanks in advance,
Fernando
Jugal Shah said
Wait.. Can you tell me what exactly you want to do? You want to change the computer name or instance name?
Please checkout my article http://www.mssqltips.com/sqlservertip/2525/steps-to-change-the-server-name-for-a-sql-server-machine/ for more information.
Fernando said
I was in need of changing the instance name. But now i have opted with computer name change (cname). The article was useful and Thanks for the suggestion.
Ashish said
You can change the instance name by this technique but while connecting to the server, you still need to mention the old name.
Though its surprising that when you run @@servername, it will give the new changed name. But if you try to connect with this new name, you will get error.
Jugal Shah said
Hi Ashish, You can checkout my article on MSSQLTips.com http://www.mssqltips.com/sqlservertip/2525/steps-to-change-the-server-name-for-a-sql-server-machine/ for more information.
You can’t change the instance name alone, you have to change the machine name as well. SQL Server name would be always machine name/mssqlserver for default instance and for named instance machine name\instance name. Let me know if you need more information.
sandeep sonawane said
hi Jugal,
This is the exact issue with me.
I mine is a named instance of SQL2k8R2 and incorrectly named same as that of the servername. Hence making it “YSIINCONV2\YSIINCONV2″ which I want to rename as “YSIINCONV2\SQL2k8R2″.
So, in this case, why will I change my servername if it is not required? I want to change the instance name only. I have used sp_dropserver and sp_addserver which is showing results properly with sp_helpsever and select @@servername. But I need to connect using the old instance only.
Please guide about what can be done. I dont want to uninstall and reinstall the instance, but still want to rename it as “YSIINCONV2\SQL2k8R2″
Thanks a lot in advance.
–Sandeep.
Jugal Shah said
Try to create the alias name and connect using that.