HOW TO: Import/Export SQL Server Maintenance Plan

As sqlmaint Utility will be phased out in future versions of SQL Server, Maintenance Plan is Microsoft's recommended approach for Database Maintenance tasks by the DBA

• Check Database Integrity
• Shrink Database
• Reorganize Index
• Rebuild Index
• Update Statistics
• Clean Up History
• Execute SQL Server Agent Job
• Backup Database (Full, Differential, Transaction Log)
• Maintenance Cleanup Task

Naturally, after spending the time and efforts to setup proper Maintenance Plans, you should save it and re-use elsewhere anytime you can. And here are 2 ways to export the Maintenance Plan in SQL Server 2005 and 2008

The easier GUI way
1. Open SQL Server Management Studio

2. In Object Explorer (left pane), click "Connect", choose "Integration Services..."

3. Login into the SQL Server with credentials

4. Expand SERVERNAME (Integration Services), and you'll see the below structure

• Running Packages
• Stored Packages
• File Systems
• MSDB
• Maintenance Plans <- this is what you want
  

5. Right Click on the Maintenance Plan you want to export, and select Export
 
The harder script way


USE MSDB

SELECT name
,PlanXML=CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML)
FROM sysdtspackages90