Maintenance plans fail after instance rename in SQL Server 2008

In the last few weeks, I had seen a couple of questions regarding Maintenance Plans in SQL Server 2008. On these servers SQL Server 2008 was installed. After that due to some reasons the Server Name was changed. Accordingly the instance name was also changed using a script similar the one below.

sp_dropserver 'OldInstanceName'
GO
sp_addserver 'NewInstanceName', local
GO

Everything seems to work fine except the Maintenance Plans which were created prior to renaming the servers. The jobs keep failing with an error that the server name could not be found. The problem is that the Maintenance Plans keep referring to the old server name.This is because the Maintenance Plans are in turn SSIS packages that get stored in the MSDB. Renaming the server does not change the connection strings inside these packages. As of now there is no option in SSMS to fix this. There is a script contributed by the SQL Server community in this article on MSDN which does the job.

--Source: http://msdn.microsoft.com/en-us/library/ms143799(SQL.100).aspx
use msdb
DECLARE @oldservername as varchar(max)
SET @oldservername='<server name><instance name>'

-- set the new server name to the current server name
declare @newservername as varchar(max)
set @newservername=@@servername

declare @xml as varchar(max)
declare @packagedata as varbinary(max)
-- get all the plans that have the old server name in their connection string
DECLARE PlansToFix Cursor
FOR
SELECT    id
FROM         sysssispackages
WHERE     (CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX)) LIKE '%server=''' + @oldservername + '%')

OPEN PlansToFix

declare @planid uniqueidentifier
fetch next from PlansToFix into @planid
while (@@fetch_status<>-1)  -- for each plan

begin
if (@@fetch_status<>-2)
begin
select @xml=cast(cast(packagedata as varbinary(max)) as varchar(max)) from sysssispackages where id= @planid  -- get the plan's xml converted to an xml string

declare @planname varchar(max)
select @planname=[name] from  sysssispackages where id= @planid  -- get the plan name
print 'Changing ' + @planname + ' server from ' + @oldservername + ' to ' + @newservername  -- print out what change is happening

set @xml=replace(@xml,'server=''' + @oldservername + '''','server=''' + @newservername +'''')  -- replace the old server name with the new server name in the connection string
select @packagedata=cast(@xml as varbinary(max))  -- convert the xml back to binary
UPDATE    sysssispackages SET packagedata = @packagedata WHERE (id= @planid)  -- update the plan

end
fetch next from PlansToFix into @planid  -- get the next plan

end

close PlansToFix
deallocate PlansToFix
----- This will also handle the packages that have a tag such as
----- <DTS:Property DTS:Name="ConnectionString">Data Source=servername;Integrated Security=SSPI;Connect Timeout=30;</DTS:Property>

DECLARE @oldservername as varchar(max)
SET @oldservername='<server name><instance name>'-- set the new server name to the current server name
declare @newservername as varchar(max)
set @newservername = @@servername
declare @xml as varchar(max)
declare @packagedata as varbinary(max)-- get all the plans that have the old server name in their connection string
DECLARE PlansToFix Cursor FOR
SELECT id
FROM sysssispackages
WHERE (CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX)) LIKE '%Data Source=' + @oldservername + '%')

OPEN PlansToFix
declare @planid uniqueidentifier
fetch next from PlansToFix into @planid
while (@@fetch_status<>-1) -- for each plan
begin
if (@@fetch_status<>-2)
begin
select @xml=cast(cast(packagedata as varbinary(max)) as varchar(max))
from sysssispackages where id= @planid -- get the plan's xml converted to an xml string
declare @planname varchar(max)select @planname=[name] from sysssispackages where id= @planid -- get the plan name
print 'Changing ' + @planname + ' server from ' + @oldservername + ' to ' + @newservername -- print out what change is happening
set @xml=replace(@xml,'Data Source=' + @oldservername,'Data Source=' + @newservername) -- replace the old server name with the new server name in the connection string
select @packagedata=cast(@xml as varbinary(max)) -- convert the xml back to binary
UPDATE sysssispackages SET packagedata = @packagedata WHERE (id= @planid) -- update the plan
end
fetch next from PlansToFix into @planid -- get the next plan
end
close PlansToFix
deallocate PlansToFix
--Source: http://msdn.microsoft.com/en-us/library/ms143799(SQL.100).aspx

After executing the script all the Maintenance Plans are refreshed to include the current Instance Name in the connection string. I will update this post, if I come across a better solution.

2 thoughts on “Maintenance plans fail after instance rename in SQL Server 2008

  1. Raj

    Interesting post Pradeep. Thanks.Just curious , is the SQL Server patched or its at RTP.
    With SQL 2005 RTP, i think i faced a similar issue and it was alright after patching.
    Regards,
    Raj

Comments are closed.