SQL Server – How to Script a Maintenance Plan

Maintenance plans are stored in msdb.dbo.sysssispackages like any other SSIS packages that are stored to SQL Server. I have a handy post on SSIS Package Extract from MSDB that should cure what ails you.

You need to change the owner of the maintenance plan. It was set up to be owned by an account that no longer exists. Not sure if it is better in your scenario to use sa or the new local admin account. This update assumes you still have the sqladmin account after the rename and that it has been added as a login to SQL Server with admin privileges.

UPDATE msdb.dbo.sysssispackages
  SET ownersid = SUSER_SID('VM-MSSQL-2008R2\sqladmin')
  WHERE ownersid = SUSER_SID('WIN-6QFI9JAK804\sqladmin');

You may also need to reset the owner of the job associated with the maintenance plan, which you can do through the UI or via the following:

EXEC msdb..sp_update_job
  @job_name = N'MaintenancePlan.Subplan_1', -- or whatever it's called
  @owner_login_name = N'VM-MSSQL-2008R2\sqladmin';

If the login hasn't been added, then execute this first:

CREATE LOGIN [VM-MSSQL-2008R2\sqladmin] FROM WINDOWS;
GO
EXEC sp_addsrvrolemember N'VM-MSSQL-2008R2\sqladmin', N'sysadmin';

In the future, be sure to have the machine properly configured / named etc. before installing SQL Server. :-)