Maintenance plans don't get their connections changed to the new server name and so they may break. After a rename you may find that you cannot delete or rename the existing maintenance plans, so either delete them before renaming the server and recreate them afterwards or run the following script to fix them:
Note: This mostly worked for me, except I had to use the sysssispackages table instead of sysdtspackages90 table.
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 sysdtspackages90
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 sysdtspackages90 where id= @planid -- get the plan's xml converted to an xml string
declare @planname varchar(max)
select @planname=[name] from sysdtspackages90 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 sysdtspackages90 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 sysdtspackages90
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 sysdtspackages90 where id= @planid -- get the plan's xml converted to an xml string
declare @planname varchar(max)select @planname=[name] from sysdtspackages90 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 sysdtspackages90 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