How to change SQL Server Instance Name?

53 Replies

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
Advertisements

53 thoughts on “How to change SQL Server Instance Name?”

  3. Umesh

    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

    Reply ↓

  4. Pingback: 2010 in review « SQLDBPOOL.COM

  6. chandra

    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

    Reply ↓
  7. Charlie

    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.

    Reply ↓
      1. Charlie

        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.

  8. binal pandya

    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?

    Reply ↓
  11. ami

    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???

    Reply ↓
    1. Jugal Shah Post author

      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.

      Reply ↓
      2. Jugal Shah Post author

        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’

  12. Fernando

    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

    Reply ↓
      1. Fernando

        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.

  13. Ashish

    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.

    Reply ↓
      1. sandeep sonawane

        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.

    1. Jugal Shah Post author

      Right My Computer and change the windows server name. and to change the SQL Server name follow the my article how to change the SQL instance name,

      Reply ↓
  16. kumar

    sir,i was uninstall the sql server 2005 because of some files missing.now i try to install the sql server 2005 again,it’s shows named instance “SQLExpress”,click on next button,it’s not move forward …please give the solution

    Reply ↓
  17. Annis

    This is very interesting, You are a very skilled blogger.

    I have joined your feed and look forward to seeking more
    of your fantastic post. Also, I’ve shared your site in my social networks!

    Reply ↓
  18. preethi

    Hi jugal i have downloaded one project which is in .net and sql server 2005 and trying to execute getting some error like network related or instance-specific error occured while establishing the connection to sql seever.the server not found or not accessible,verify that instance name is correct and that sql is configured to allow remote connections…i have installed sql-2005 n instance name is SQLEXPRESS..PLZ HELP ME

    Reply ↓

  19. Pingback: sql server change instance name 2008 r2? | Askjis

  22. purnachandra

    I am first time installing SQL SERVER, I dont want default instance name(MSSQL SERVER),can we make it as Named Instance.

    Reply ↓
  24. Chreddy

    If it is a default instance, then how the SQL Server instance name will change? Do we need to change the host name before changing the SQL Server instance name? If not so, then what connection name I can use to connect to SQL Server instance?

    Reply ↓
  25. Pavan

    The server ‘repl_distributor’ is already defined as a Distributor. To reconfigure the server as a Distributor, you must first uninstall the exisiting Distributor. Use the stored procedure sp_dropdistributor, or use the Disable Publishing and Distribution Wizard.

    Reply ↓
  27. Chandu

    Hey Jugal!!

    I am getting this ERROR”Transaction context in use by another session.” while I am trying to execute the SP’s across the Linked Servers.Please help me on this

    Regards,

    Chandu

    Reply ↓

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Google photo

You are commenting using your Google 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 )

Cancel

Connecting to %s