SQL Server 2005 how to copy database maintenance plans between the servers?
How can you copy the database maintenance plans from one SQL Server instance to another SQL Server instance using 2005 version.
Using the Database Maintenance plans has been increased since SQL Server 2005 is released, also lot more once Microsoft fixed the issues with Service Pack2 for SQL 2005. In general you can take help of Copy Database Wizard using copy of correct SSPI packages. WHen the operation is successful then ensure you have relevant SQL 2005 client tools (SSMS) with Service pack2 in order to see those Legacy maintenance plans to view. Also you can connect to Integration Services in the SQL Server Management Studio (SSMS) and when you connect to there you will see :
Stored Packages - Expand this to see
- File System
- MSDB
- Maintenance Plans
Then you will see the Maintenance plans, and you can right click on the one you want and Export the package.
Once the packages are exported then connect to the server to Import the package from the exported file. IN this case you have two options. Maintenance Plans are simply SSIS packages, so if you make them generic enough you can deploy the package to any of your servers. Further the reference on performing this action by using SMO refer to DBMaintPlansCopy_usingSMO