SQL Server 2008 Maintenance Plan Errors When Changing Server Name

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