Header

Friday, November 16, 2007

Import and Export SQL 2005 Maintenance plans

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.

2 comments:

João said...

How about "Managed Connections" for that plan you import. It has a Name="Local server connection" that points to the originating server. Can that one be changed or must you have that one as a potential error source when editing plans on the "imported" server side?

Rasmus Glibstrup said...

The "Local Server Connection" points to a server named "." - which is a synonym for Localhost. So generally this should not be a problem when moving maintenance plans.