Renamed a SQL Server

Question

Post reply

Renamed a SQL Server

Summer90

SSC-Dedicated

Points: 32891
More actions
April 23, 2010 at 7:50 am

#218163

We had to rename a SQL Server and now the maint plans cannot be deleted or jobs cannot run... Get error of login failed for user... the user not associated with a trusted SQL Server connection....
In SQL 2000 if a sql server was renamed we had to go into msdb and update jobs..... is this the same type of thing in SQL 2005?
We are on SQL 2005 SP3 win 2003 sp2

Viewing 6 posts - 1 through 5 (of 5 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.

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