August 27, 2010 at 9:39 am
Hi,
So the IT guys renamed the server and then updated the name in SQL. Not sure of the method but
@@ServerName is showing the new server name.
The problem now is that the maintenance plans are failing because they cannot connect to the old server name. When you open the plans, the local connection is showing the old server name and there doesn't appear to be a way to update it. Logging in both as Windows and SQL Authentication makes no difference. The Name is grayed out and the ellipses button is not active.
Our next step is to delete the plans but that fails via the GUI as we can't connect to the old
server name.........
Anybody know what table in msdb holds the plan information. Already have deleted the sub_plans and the jobs but can't get rid of the maintenance plans in he GUI.
Have done some research but most relates to changing the login name and not the server name....
Thanks in advance for any ideas.
Dave
August 27, 2010 at 9:50 am
I followed this link[/url] on a SQL 2000 server that had the same issue. It holds true to SQL 2005 as well.
sysdbmaintplans should be the table you are looking for though.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
August 27, 2010 at 10:01 am
No luck there. Sysmaintplans has one record and it looks like it's a default from the installation of SQL Server.
I removed the old jobs from the old plans but the plans still remain in the GUI.
I've create a new plan as I thought it'd be nice that they have a current backup and that works fine.
August 27, 2010 at 11:42 am
Looked a little bit more and found this under the System Views
use MSDB
select * from dbo.sysmaintplan_plans
This shows the plans from the GUI and I was able to delete the old plans.
I wonder if this info is stored somewhere else and is now just sitting out there though.
August 27, 2010 at 12:45 pm
Hi DMarvez,
Actuall after the Server Rename and SQL Rename you need to update all the Jobs to point them to the new server name because even after changing the SQL name to the New Name the Jobs still point to the Old Server Name. They have to be manually changed using the following Script
USE msdb
GO
DECLARE @server sysname
SET @server = CAST(SERVERPROPERTY('ServerName')AS sysname)
UPDATE sysjobs
SET originating_server = @server
WHERE originating_server = '<wrong_servername>'
You should do this to the MaintPlan Jobs too. And then Local in the MP should Point out to the right servername.
Thank You,
Best Regards,
SQLBuddy
August 27, 2010 at 4:22 pm
sqlbuddy123 (8/27/2010)
Hi DMarvez,Actuall after the Server Rename and SQL Rename you need to update all the Jobs to point them to the new server name because even after changing the SQL name to the New Name the Jobs still point to the Old Server Name. They have to be manually changed using the following Script
USE msdb
GO
DECLARE @server sysname
SET @server = CAST(SERVERPROPERTY('ServerName')AS sysname)
UPDATE sysjobs
SET originating_server = @server
WHERE originating_server = '<wrong_servername>'
You should do this to the MaintPlan Jobs too. And then Local in the MP should Point out to the right servername.
Thank You,
Best Regards,
SQLBuddy
That only works in SQL2000. In SQL2005 and above originating_server = 0.
I am very dubious about msdb restores across servers or server renames with msdb, especially where SSIS is involved (hence the problems with maintenance plans)
---------------------------------------------------------------------
August 27, 2010 at 10:49 pm
In SQL Server 2005 and above, maintenance plans are now stored as SSIS packages. The connection information is stored within the package.
So, your only option is to delete the plan and rebuild it - so the connection information is correct.
However, there is a way you can update the connection information without having to rebuild all maintenance plans.
1) Create a new Integration Services project in BIDS
2) From Solution Explorer, right-click on packages and Add Existing Item
3) Select SQL Server - and put in the SQL Server where the maintenance plans are
4) Select the maintenance plan to be added (it's in the Maintenance Plan folder)
5) Once the plan is loaded into BIDS, double-click on the Local Connection and change the connection information to the new server name.
a) Do not make any structural changes (e.g. add tasks, remove tasks, etc...)
b) Do not change the names of any tasks - this will break the plan
6) Go to File | Save Copy As and save back to the server
Now, you can open the maintenance plan in SQL Server and verify the connection and set the new schedule. You may need to delete the original plan before you can import the new plan - which I recommend anyways.
You can change the databases for the tasks, and path information for the tasks - but you cannot change the structure of the plans.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 1, 2010 at 10:30 am
Thanks for all of the replies.
I removed the records from MSDB which is fine for now.
I need to remember the Integration Services part when looking into this in the future instead of just focusing on the Engine.
Thanks again and I'll try some of the suggestions next time.
Dave
September 2, 2010 at 2:40 am
sysdbmaintplans
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply