Home » SQL Server » Rename or Change SQL Server Standalone Default Instance

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

About these ads

6 Comments

  1. In Step 2, how do we rename Host name? Through an update statement?

  2. Rudy Panigas says:

    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.

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

Follow

Get every new post delivered to your Inbox.

Join 228 other followers

%d bloggers like this: