July 27, 2008 at 8:50 am
Is that the same issue?
July 27, 2008 at 9:35 am
SQL Server Central thread:
"Under SQL Server 2005, we have found that we are having a problem with our maintenance plans. Since most of our servers are on VMWare, when we installed a new server, our system admins cloned this server and created a DR server at our DR facility. However, when we tested the DR server, the maintenance plans were running on the original server. The connections for the maintenance plans were still point to the original server, and not the DR server."
Microsoft Connect:
"After renaming a SQL Server, one must go in an edit all of the connections for a Maintenance Plan to coincide with the new SQL Server name."
Experts Exchange thread:
"I have production server 'A' and virtual server 'B'. I took the master, msdb, model, and db1 backups from server 'A' and restored them to server 'B'. I then ran the sp_drop server 'a' and then sp_addserver 'B' on server 'B'. The maint plans were created on server 'A' to use local connection, however when they are on server 'B' SQL Agent, the maint plan jobs execute against server 'A'."
Isn't all these three scenarios related to how the connection is stored in the Maintenance Plans? It seems like it is the orginal sql server name that is stored and then when you do things like restoring system databases on another server, renaming the server or cloning the server, things doesn work as expected.
This is just my analysis. It could be that there is something that I don't understand.
Ola Hallengren
July 28, 2008 at 9:36 am
Sorry I have not replied to anyone, I was out of the office last week. I agree that all of these issues sound like mine. However, if I use the SMS Maintenace plan screen to try and change the connection, it will allow me to add a connection, but not to delete nor change the original connection. Also the only way I know to view the maintenance plan data is to use the Dynamic View sysmaintplan_plans. I assume this means the maintenance plan data is stored in the resource database, and therefore cannot be updated.
July 28, 2008 at 10:45 am
My understanding is that the SQL Server 2005 Maintenance Plans are stored as SSIS packages in the system table msdb.sysdtspackages90.
I have a set of maintenance stored procedures that you could use if you like.
Ola Hallengren
July 28, 2008 at 10:56 am
Yes, there is one row for each Maintenance Plan, however the packagedata column is in a hex format.
July 28, 2008 at 11:29 am
It is stored as a SSIS (SQL Server Integration Services) package.
I think that you have to use SQL Server Business Intelligence Development Studio to change it. This is however not my field, so I will not be able to help you with this.
Ola Hallengren
July 28, 2008 at 1:17 pm
Unfotunately, we do not have BI installed. For tht matter, we do not have SSIS installed. The only person who uses DTS\SSIS to transfer data on a regular basis is using Analysis Services and he is not a major application.
July 28, 2008 at 1:24 pm
Not sure if this is still true, but once I had edited maintenance plans in pre-SP2 servers with SSIS, I couldn't open them in the maintenance plan designed and had to use SSIS for all changes.
July 28, 2008 at 1:37 pm
Brian,
I didn't see any specific versions of SQL Server mentioned. What version are your Production and DR servers running for SQL Server 2005? SQL Server 2005 Service Pack 2 (9.00.3042) seems to be the minimum standard for patching SQL Server 2005 systems. Also note that if you are remotely connecting to a SQL Server Instance, you need to also patch the client machine. (This reared it's ugly head in my new environment).
Hope This Helps,
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
July 28, 2008 at 1:42 pm
We are on SQL Server 2005 SP2 Cumulative Update Package 3
January 13, 2009 at 1:35 pm
We have the exact same issue. There does not seem to be a way to resolve the maintenance plan
running against the 'source' server and scripting the maintenance plan out does not seem to be
viable.
January 13, 2009 at 2:11 pm
We did find an excellent posting with a tsql script that may script out the maintenance plans.
We are modifying it and going to try to 'script in' the maintenance plans instead of restoring msdb.
February 26, 2010 at 4:41 am
Tip:
You can create a alias on "sqlserver configuration manager" tool that point to old server name 😉
regards,
Thiago
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply