SQL Server 2008 Maintenance Plan Errors When Changing Server Name
Posted by Arie | Posted in SQL Server | Posted on 20-07-2009
Tags: changing server name, error, maintenance plans, sql 2005, sql 2008, SQL Server 2005, SQL Server 2008
2
I recently ran into a bug issue here recently when I had to update a server name on a database box running SQL Server 2008. I updated the server name and then set SQL Server to run under the new name as well and restarted the system.
The strange thing was that I went to immediately run one of the maintenance plans to get backups of the database system….it failed. Hhhhmmmm,interesting. Why would it fail now? The error log said that the problem stemmed from the Local Server Connection being unrecognized.
Normally, when you create a maintenance task within the instance there is a connection that is set up automatically set to the local instance like shown below.
This is supposed to make it easier when setting up the jobs and moving it between servers. Since it is set to localhost then it wouldn’t have to be updated or anything. So why is this one different?
After some thinking, I went and looked at the maintenance plan local server connection value. There is where I found the answer. The local server connection showed something similar to below.
The localhost connection was actually a named connection. The thing is that if I create another job on the SQL Server 2008 instance it does not replace localhost with the server name. So this isn’t a bug with SQL Server 2008 maintenance plans. However, this machine was upgraded from SQL Server 2005 a couple of months back. …Doing some quick investigating using a SQL Server 2005 machine I had handy I confirmed this to be the case. Unfortunately, after messing around with it for a little while I also found out that it appears the only way around it is to delete the job and recreate it. Bummer.
So if you are upgrading from SQL Server 2005 to SQL Server 2008 this may be one of the things you have to watch out for. It won’t necessarily stop you from upgrading right away as the it appears that the process will update the name of the connection when upgrading. However, if the name is changed from the original server name…..that is where it will bite you.
Hopefully, this saves you a little bit of time if you run into this issue.
Cheers,
AJ
That name can be changed. If you connect to integration service, then you can actually export and then import the maintenance plan.
You can even save the maintenance plans in a file (choose filesystem when exporting), Edit the file and replace your server name with the new one. Just search for your old server name and replace with new one. Save the file and import the file back using the integration service. Hope you have intergation service installed. Choose connect to and see if it is installed. I hope it works for same server.
I am doing the same but to different SQL instances and struggling with migrating the identities keys.
Raj,
While this is true …you would think that they would provide you a way in which you could easily accomplish this through the maintenance window without having to do some XML juijitsu.
You are correct though and it may actually be easier if you open bids and create a new integration services project. That will allow you to add the existing file to the set ..delete the prefab one…change your connection strings and then build it with the option to create a deployment utility.
So this technique might actually help you along with your problem you have in migrating them across entities.
Cheers,
AJ