Renaming a SQL Server Instance

I know that this script is ubiquitous across the ‘net, but whenever I google for it I come up with elaborate stored proc’s that are overkill for my needs – so here are the commands necessary to rename a SQL Server instance, for posterity:

– Get the current name of the SQL Server instance for later comparison.
SELECT @@servername

– Remove server from the list of known remote and linked servers on the local instance of SQL Server.
EXEC master.dbo.sp_dropserver ‘[SERVER NAME]‘

– Define the name of the local instance of SQL Server.
EXEC master.dbo.sp_addserver ‘[NEW SERVER NAME]‘, ‘local’

– Get the new name of the SQL Server instance for comparison.
SELECT @@servername

And a few notes relating to their usage:

  • sp_dropserver: 1. this stored procedure can remove both remote and linked servers; 2. using the droplogins parameter indicates that related remote and linked server logins for [SERVER NAME] must also be removed. More info.
  • sp_addserver: 1. to define a linked server, use sp_addlinkedserver (as this functionality will be depreciated in version above SQL Server 2005); 2. sp_addserver cannot be used inside a user-defined transaction. More info.

I would also recommend the following when renaming a SQL Server:

  • If you are performing this task as part of a machine rename, rename the machine first then rename the SQL Server.
  • Once you have renamed the SQL Server, do a full restart of SQLServer, including any dependant services.
Reblog this post [with Zemanta]
About these ads

18 thoughts on “Renaming a SQL Server Instance

  1. Hello, I tried it..

    If I do this: “select * from sys.servers” it looks good, but when I try to log on to the new INSTANCE name it won’t work.. the old one I can still connect to. I did a reboot of the system..

    What am I missing here?

  2. Kosm/Pavel,
    I’m sure you did, but can you confirm that you successfully ‘dropped the server’ before adding a new server?

    Nick.

  3. I just tried it. select @@servername shows the new name. I re-booted the machine and logged back in. When I use management studio and tell it to browse for instances on the local machine, it still shows the old name, even though @@servername is what I want to see…

    I did the drop server and it worked, so help!

    Thanks,
    Mark

  4. Same happened to me.

    I have been searching the web for weeks now on how to install SQL Server Express as a named instance and all the config.ini settings I tried and command prompt params I tried does not work. Can anyone help with a sample .ini file that actually works. At this stage it only opens up the command prompt if I try the .ini file and nothing happens

  5. Same problem for me… I follow the instructions and in SS Configuration Manager the old name still appears. You migt as well remove this post from the Internet since it doesn’t work.

  6. i wouldn’t recommend following this blog article. the blog article fails to update the sql instance directory name (as well as the references inside each database to that directory).

  7. I agree , if you run “select @@servername” the new name is displayed. However if you want to register the new instance, only the old name is displayed . Even a reboot after the name change does not help .

  8. I have a doubt. I´m using SQL Server 2008 and i want to change a table name from Reveita to Receita.

    How can I do that?

    Thanks

  9. @Nick Heppleston’ article. The question is how do we make use of the server name thats shown in “SELECT @@servername”, kinda, if I want to connect to the sever using that new name, how do I connect?

  10. This did not work, it was easier just to install an other instance. This changed the name of the instance in the master DB, but the name must be in other places as well.

  11. Dears,
    I tested above mentioned procedure to rename the SQL Server 2008 instance and it really works. You have to adopt the following instructions sequentially.
    >> Rename Machine’s name (Your PC/Server Name)
    >> Execute the following commands in SQL query window:

    — Get the current name of the SQL Server instance for later comparison.
    SELECT @@servername

    –Remove server from the list of known remote and linked servers on the local instance of SQL Server.
    EXEC master.dbo.sp_dropserver ‘[SQL SERVER NAME]‘

    –Define the name of the local instance of SQL Server.

    EXEC master.dbo.sp_addserver ‘[NEW SERVER NAME]‘, ‘local’

    — Get the new name of the SQL Server instance for comparison.
    SELECT @@servername

    Note: Be executing these commands, you will see new Server name but actually job is not done completly.

    >> Restart the System or all SQL related services.

    >> Now connect with SQL Server using old SQL Instance and credentials. It will work else use some other instance like ‘local’ etc.

    >> Go to Security -> Logins, create new login with new Machine name. For example: [NEW MACHINE NAME]/[LOGIN NAME] => WIN-SERVER/Administrator.

    >> After creating login, delete the existing (older) login and try to connect with new login name…. It must work!!!

    Be happy.

  12. use master
    exec sp_dropserver @@SERVERNAME,’droplogins’
    exec sp_addserver ‘NEW_MACH_NAME’,’local’

    If you use distribution execute this command as well.

    use msdb
    update sysjobs set originating_server =’NEW_MACH_NAME’

    Restart SQL Services.

  13. I have changed the instance name (ex: from servernameA to servernameB) using above script and restart machine. While execute “select @@servername”, display new instance name. but while connect using new name it’s not connected (SSMS–> Connec –> ServernameB).. Pls help me how to resolve this…

  14. Web keyword search Microsoft SQL Server 2008 Change Server Name security steps
    Good blog, took a while to find it.
    It might be worth a note: Make a full backup of the SQL Server database in case it needs to be restored on the new server.

    Would one solution be to create the new server name instance (i.e. ASQLDBName and BSQLDBName)
    Install SQL Server on instance B then use the SQL Server migration tools to transfer the objects from ASQLDBName to BSQLDBName
    There appear to be a lot of variables to simply changing the server name. Maybe setting up the 2nd instance and transferring objects would be faster with more predictable results?

  15. This is completly ridiculous…

    FYI STEEVE
    update sysjobs set originating_server =’NEW_MACH_NAME’

    wrong collomns originating_server whould be originating_server_id

    where did u get that info? show me the link from msdn
    the datatype for that collomns is an Integer ….
    good luck to add a ’NEW_MACH_NAME’ in that place.

    this is a garbage info

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