Wednesday, September 3, 2008

How to change SQL Server Instance Name?

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.
[sourcecode language="sql"]
sp_helpserver
select @@servername
[/sourcecode]
You can change the instance name using below query.
Default Instance
[sourcecode language="sql"]
sp_dropserver 'old_name'
go
sp_addserver 'new_name','local'
go
[/sourcecode]

Named Instance
[sourcecode language="sql"]
sp_dropserver 'Server Name\old_Instance_name'
go
sp_addserver 'ServerName\New Instance Name','local'
go
[/sourcecode]

Verify sql server instance configuration by running below queries
[sourcecode language="sql"]
sp_helpserver
select @@servername
[/sourcecode]

Restart the SQL Server Services.
[sourcecode language="sql"]
net stop MSSQLServer
net start MSSQLServer
[/sourcecode]
Posted by at
Labels: ,

13 comments:

  1. sandeepJune 16, 2009 at 12:07 AM

    can we create two instances in sql server standard edition?

    plz explain.

    ReplyDelete
  2. JShahJune 17, 2009 at 12:39 AM

    Yes we can create two instances in standard edition

    ReplyDelete
  3. online stock trading adviceJanuary 10, 2010 at 5:00 PM

    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! :)

    ReplyDelete
  4. 2010 in review « SQLDBPOOL.COMJanuary 1, 2011 at 11:17 PM

    [...] 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 [...]

    ReplyDelete
  5. GbakshMarch 19, 2011 at 2:20 PM

    thanks a lot for this tip. It helped me a great deal.

    ReplyDelete
  6. chandraMarch 27, 2011 at 9:03 AM

    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

    ReplyDelete
  7. Jugal ShahApril 4, 2011 at 11:00 PM

    Log file shouldn't be too big, if you have scenario go for compress backup. Again you can change the interval as well.

    ReplyDelete
  8. CharlieJuly 15, 2011 at 1:33 PM

    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.

    ReplyDelete
  9. Jugal ShahJuly 26, 2011 at 11:36 PM

    Can you please check the registry as well? Is it a default instance or named instance?

    ReplyDelete
  10. CharlieJuly 27, 2011 at 6:55 AM

    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.

    ReplyDelete
  11. binal pandyaAugust 2, 2011 at 5:28 AM

    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?

    ReplyDelete
  12. Jugal ShahAugust 4, 2011 at 10:29 AM

    Try to install the named instance...

    ReplyDelete
  13. KamleshOctober 6, 2011 at 11:31 PM

    Thanks ..
    It really work but you must add 1 step to restart the server


    thanks again ...

    ReplyDelete

Subscribe to: Post Comments (Atom)