Renaming SQl Server machine and the database

Question

Renaming SQl Server machine and the database

vijay245

SSC Enthusiast

Points: 103
More actions
December 15, 2014 at 2:19 pm

#296036

We are running Sql Server 2005. We have about 100 SSI packages which runs based on the old Sql server name.
I have moved the msdb databases from the old machine and I am able to see all the packages. However I cant run any since they are based on old sql server name. But they work if I export them and re-configure them.
So I need to rename the sql server and the database so that I can run all the sql integration packages without any changes. What is the best procedure the rename the machine and the server. The server I am moving to is Windows server 2007
Can I rename the machine first and then drop and change the database instance name?
Thank you

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Shafat Husain SSCrazy Points: 2754 More actions · Answer 1

Dear Vijay,

There is no need to rename the machine as your SSIS packages would be connecting to the instance name of your SQL Server.

As you have not mentioned if you have a default instance or a named instance, I would suggest that you look for the syntax for using

Sp_dropserver and sp_addserver.

Here is a gud link

# http://www.mssqltips.com/sqlservertip/2525/steps-to-change-the-server-name-for-a-sql-server-machine/

Hope it helps..!!!

Regards

Shafat Husain

Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)

vijay245 SSC Enthusiast Points: 103 More actions · Answer 2

thank you for your reply. We don't have instance running. It is just the default. I have not yet renamed the server or the mssql SQL. I don't want to end up in 2 names.

1. Can we do it from remote desktop? I have admin access.

2. SELECT SERVERPROPERTY ('InstanceName') returns Null We haven't configured reporting server either.

So is there anything else I need to do apart from renaming the machine and running the following sql. Is the syntax correct?

sp_dropserver 'sqlprod_1'

--Add the current server.

sp_addserver 'sqlprod_2', local

Thank you

Shafat Husain SSCrazy Points: 2754 More actions · Answer 3

Dear Vijay,

If you only have a default instance then the name of the SQL Server Instance would be your machine name. You can check with the listed query.

select @@servername

Copy the name of the instance from the output of the above query and drop replace it with the desired name.

sp_dropserver 'CURRENT_NAME'

sp_addserver 'DESIRED_NAME', local

Run the query again to check if the new name is updated or not.

Regards

Shafat Husain

Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 4

FYI: This works... however, you must stop/start SQL Server for the servername within SQL server to take effect.

vijay245 SSC Enthusiast Points: 103 More actions · Answer 5

Thank you everyone. I am also changing the name of the server it self. Do I need to do this after rebooting the machine with the DNS name change or can I do this at the same time (both DNS name and SQL instance name) and reboot the machine later?

Thanks again

Shafat Husain SSCrazy Points: 2754 More actions · Answer 6

Dear Vijay,

You can change it in one go. No need to wait for the restart.

Regards

Shafat Husain

Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)