SQLServerWiki

“The Only Thing That Is Constant Is Change”

OLD-SERVER\OLD-INSTANCE to NEW-SERVER\NEW-INSTANCE

Posted by database-wiki on March 6, 2011

Issue:

I have two severs. I need to make my backup server look exactly like the original but with a different server and SQL instance name.  I will use the DR Bare Metal Restore to make this copy.  But I will change the name of the server and would like to change the name of SQL Server Instance as well.  The copy of the SQL server instance has to be different because these two SQL instance will co-exist on the same network.

How to proceed?

We can separate the entire scenario in to 2 parts.

1.      Restoring and Renaming the Windows 2008 Server:

How are we going to restore the DR Bare Metal Backup. Since the original server is already on the network we have to make sure that while restoration process the server should be specified a different name or if we don’t have such an option we need to remove the new server from the network, then restore the DR Bare Metal Backup on it and then change the server name by following the below KB.

How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server2005 ( Just go the server property of that box and change the computer name)
http://msdn.microsoft.com/en-us/library/ms143799(SQL.90).aspx

2.      You need to complete step 2 as well to before connecting the new server over the network.

Renaming the SQL Server 2005:

1. Performing sp_dropserver and sp_addserver

sp_dropserver <old_name>
GO
sp_addserver <new_name>, local
GO

Restart the SQL Server instance.

[Note]:
i. To verify that the renaming operation has completed successfully Select information from either @@servername or sys.servers. The @@servername function will return the new name, and the sys.servers table will show the new name.

2. Issues with Remote Logins

If the computer has any remote logins, running sp_dropserver may generate an error.

To resolve the error, you must drop remote logins for this server.

sp_dropremotelogin old_name
GO

3. For the default 3 SQL logins created by SQL server, drop them and re-add them with the new names

<Server>\SQLServer2005MSFTEUser$<SQLServer>$<instance>
<Server>\SQLServer2005MSSQLUser$<SQLServer>$<instance>
<Server>\SQLServer2005SQLAgentUser$<SQLServer>$<instance>

[Note]:
If these logins have corresponding schemas:

A) If the schema doesn’t contain any objects, please remove it and re-create a one with new name

B) If the schema contains any objects,

i. Create a new schema
ii. ALTER SCHEMA to move objects from the old schemas to the new schema and then remove the old schema

4. If you have linked server to the renamed server, once you have done the rename, check your linked server to see if it is working properly.
You may need to use sp_addlinkedserver and sp_setnetname on other instances to reflect the name change.

5. If you have maintenance plans on the server, once you have done the rename, check your maintenance plans to see if they are working properly.

You may need to do the following to reflect the name change if maintenance plans are not working

(a) Open Management Studio -> Management -> Maintenance Plans -> right click on the any problematic maintenance plans and click Modify
(b) In the design pane, double click on each task and change the connection. (You can create a new connection to update the correct server name)
(c) Save the maintenance plan and execute it to check if it works.

Additional information
====================

How to: Rename a SQL Server 2005 Cluster Virtual Server
http://msdn.microsoft.com/en-us/library/ms345235(SQL.90).aspx

Renaming a Report Server Computer
http://msdn.microsoft.com/en-us/library/ms345235(SQL.90).aspx

CREATE SCHEMA (Transact-SQL):
http://msdn.microsoft.com/en-us/library/ms189462.aspx

DROP SCHEMA : http://msdn.microsoft.com/en-us/library/ms186751.aspx
ALTER SCHEMA: http://msdn.microsoft.com/en-us/library/ms173423.aspx

sp_addlinkedserver (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms190479(SQL.90).aspx

sp_setnetname (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms190318(SQL.90).aspx

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

 
%d bloggers like this: