You can find maintanceplans in msdb.dbo.sysssispackages (We have upgraded from SQL2008 to SQL2016) but I want to duplicate an plan but i'm not getting it to workl I've tried this code: Insert Into msdb.dbo.sysssispackages Select 'MyNewName' As name ,NewId() As id ,description ,GetDate() As createdate ,folderid ,ownersid ,Cast(Cast(Cast(Replace(Cast(Cast(Cast(packagedata AS VARBINARY(MAX))AS XML) As NVarchar(Max)), 'Old Text', 'New text')) As Xml)AS VARBINARY(MAX)) As Image) As packagedata ,packageformat ,packagetype ,vermajor ,verminor ,verbuild ,vercomments ,verid ,isencrypted ,readrolesid ,writerolesid From msdb.dbo.sysssispackages Where packagetype = 6 And name='MyOldPackage' I get 1 row affected and when i refresh the list of maintanceplans in SSMS the new plan is listed. But when i try to edit something and save i get error that I can't save in old name and when i afterwards try to select in sysssispackages the oldplan is missing... Any suggestion what I need to add in my insert function to get it working?
AFAIK, plans cannot be duplicated like this. Lots is stored inside ssis package. Like connection properties. people have suggested exporting a plan and then using BIDS to tinker with properties but this has mixed reviews. personally I would err on the side of caution in case it starts "trippin" :)