Renamed a SQL Server

Question

Post reply

Renamed a SQL Server

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

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

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 1

If it is a standalone server you can follow this:

http://msdn.microsoft.com/en-us/library/ms143799.aspx

A test can be what hostname is returned for this query:

SELECT SERVERPROPERTY('ServerName')

SELECT SERVERPROPERTY('MachineName')

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

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

I followed the Microsoft script instructions for Maint plans and that did not seem to help. Hum..... I think I will have to call Microsoft and open a ticket with them on this.

george sibbald SSC Guru Points: 104210 More actions · Answer 3

Nope, the good old SQL 2000 update sysjobs set originating_server = 'newservername' does not work in SQL2005 and up.:(

Have you tried editing the jobs to run under a different user

You would be better off deleting and recreating the maintenance plans. These I believe are the steps to delete them (haven't tried this personally, so try in test and backup msdb first.)

-- steps to delete maintenance plans if server renamed or msdb moved to a different server

Step 1:

Execute the below query to obtain the Maintenance plan name and Id

SELECT NAME, ID FROM MSDB..SYSMAINTPLAN_PLANS

Step 2:

Replace the Id obtained from Step 1 into the below query and delete the entry from log table

DELETE FROM SYSMAINTPLAN_LOG WHERE PLAN_ID=' '

Step 3:

Replace the Id obtained from Step 1 into the below query and delete the entry from subplans table as shown below,

DELETE FROM SYSMAINTPLAN_SUBPLANS WHERE PLAN_ID = ' '

Step 4:

Finally delete the maintenance plan using the below query where ID is obtained from Step1

DELETE FROM SYSMAINTPLAN_PLANS WHERE ID = ' '

---------------------------------------------------------------------

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

Awesome... and Thanks a million. That worked!!!!!!!

george sibbald SSC Guru Points: 104210 More actions · Answer 5

pleasure. thanks for posting back.

---------------------------------------------------------------------