Change instance in SQL server (2008)


1. When you clone a SQL server or create out of scripts you may ended up with same instance. You need to change the instance once the SQL server is installed.

Important: Make sure the clone server has drive letter E:\ or same as the parent server. By default, SQL service cannot start if the drive is not E:\.

Firstly, make sure the sql service and sql agent are started on the installed machine. If not, you may need to change the Sql service (both) login. Assign appropriate SQL account with password, once it is done SQL service will start.

To find whether your server instance is same as the current server, execute the following command on the server sql server mgmt studio:-

sp_helpserver
select @@servername

sql first

If the result is different (output will show a server name and it should be the same as the one you have installed the sql server). Execute the following command (you should include the server name in the single quotes. Leave the local as it is. Leave the quote as it is.

sp_dropserver ‘old_name’
go
sp_addserver ‘new_name’,’local’
go

sql middle

Once this is done, restart the SQL service and SQL agent service on the server

Rerun this query, make sure you the output of the query matches the server name which you have installed the sql server

sp_helpserver
select @@servername

sql last

More details:
http://coderjournal.com/2008/02/how-to-change-instance-name-of-sql-server/

Advertisements

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