Today, We will go through with the steps of renaming or changing SQL Server Standalone Instance.
Step 1 : Check Current Instance & host name
Current Host Name : admin-0783e4076
Current SQL Instance Name : admin-0783e4076
Select @@ServerName ServerName, Host_name() HostName
Step 2 : Rename Host name & reboot the server
Step 3 : Try to connect with SQL server admin-0783e4076 & you will face below error because no instance of server name [ADMIN-0783E4076] exists as server name got changed
Cannot connect to ADMIN-0783E4076.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
Step 4 : Try to connect with “.” And Re Check Instance & host name
Select @@ServerName ServerName, Host_name() HostName
We have noticed that SQL instance name is still referring to old name. We need to change it as well.
Step 5 : Drop old server name from server list of SQL
Exec sp_dropserver [ADMIN-0783E4076]
Step 6 : Add new server name as default server in server list of SQL
Exec sp_addserver [Win2K3_1],local
You can check default & other server ddetails from sys.servers.
SELECT * FROM SYS.SERVERS
Step 7 : Restart SQL Services
Step 8 : Try to connect with [Win2K3_1] And Re Check Instance & host name
You are done !
If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun
Reference : Rohit Garg (http://mssqlfun.com/)
In Step 2, how do we rename Host name? Through an update statement?
You can change name of Host from My Computer properties.
Nicely done! I would like to point out that you are not necessarily done. You need to update the msdb..sysjobs so that you can modify jobs that were created with the old server name and you need to make a change to SSRS so that it too points to the new SQL Server name.
Thanks for add in.
[…] Refer : Rename or Change SQL Server Standalone Default Instance http://mssqlfun.com/2014/06/30/rename-or-change-sql-server-standalone-default-instance/) […]
[…] Refer : Rename or Change SQL Server Standalone Default Instance http://mssqlfun.com/2014/06/30/rename-or-change-sql-server-standalone-default-instance/) […]
Try to connect with “.” And Re Check Instance & host name Try to connect with “.” And Re Check Instance & host name what do you mean by this line how to connect with “.” ?? please help I changed the computer name but cannot connect to the instance to run the query
When you connect with dot . to sql server it connets to local default instance of machine.
problem is that e.g. the SQL server 2014 carry the server name deeper inside – the sql server service refuses to start if run under NT ServiceMSSQLSERVER. That you can’t fix like that, then the account under that the server runs has to be fixed too.
[…] Rename or Change SQL Server Standalone Default Instance … – Today, We will go through with the steps of renaming or changing SQL Server Standalone Instance. Step 1 : Check Current Instance & host name Current Host Name … […]
I’m so glad I found my sootiuln online.