The way SQL 2005 produces Maintenance plans is very different from SQL 2000. Instead of using the extended stored procedure xp_sqlmaint to execute and the system tables of the MSDB database to store the package – SQL 2005 uses SSIS (SQL Server Integration Services) to create and store the Maintenance plans.
To Export an existing maintenance plan from an SQL Server 2005
- Click connect in the Object Explorer
- Select "Integration Services"
- Enter credentials for the server
- Open the folder: "Stored Packages" -> "MSDB" -> "Maintenance Plans"
- Right click the package you want to export
- Select Export package
- If you want to export the package to another server or edit it – using BIDS (SQL Business Intelligence Studio) – select "File system" in the Package Placement dropdown menu.
To Import or copy an existing maintenance plan from one server to another
- On the server you want the maintenance plan copied TO.
- Click connect in the Object Explorer
- Select "Integration Services"
- Enter credentials for the server
- Open the folder: "Stored Packages" -> "MSDB"
- Right Click the folder "Maintenance Plans" and select "Import Package"
- In the dropdown "Package Placement" select SQL Server
- Enter the name of the SQL server where the source maintenance package exists.
- Click the … besides the Package Placement box and select the package you want to import from the Maintenance Plan folder.