Thursday, October 30, 2008

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

8 comments:

  1. haha, can't stop laughing at how hard the harder way is :)

    ReplyDelete
  2. dont you have to edit the connection and stuff when importing into the new sql server machine?

    ReplyDelete
  3. it seems like "the harder script way" is not finished. what do you need to do after the select?

    ReplyDelete
  4. 2008 doesn't offer a "Package Placement"....only Package Path which won't let you use an unc or other tree path....only existing sql folders.

    ReplyDelete
  5. In SQL 2008 SP1 you may need to use sysssispackages table instead.

    Thanks,

    Rob Kostecki

    ReplyDelete
  6. @RobK...thx for tip on new table name.

    Ok...so I ran query and it returns, amongst a few other records, the maintenance plan I seek to export to another SQL Server. So do I now just need to insert that value into the other server? As in:

    INSERT INTO sysssispackages (packagedata)
    VALUES ('<DTS:Executable...')

    It seems like I will probably first need to modify the packagedata value to fit the params of the new server (chiefly, the server name).

    Then I guess it might be wise to manually create the maintenance plan (so SS creates all the IDs etc), with the same name, using SSMS. Then instead of the INSERT, do an UPDATE query with the new packagedata value.

    Sound about right? Or should I skip the step to create the MP manually? And if so, has anyone developed an INSERT that takes care of adding the required IDs as well?

    - anthonyx26

    ReplyDelete
  7. Ha, even myself don't use Import/Export as I just manually create a new MP with the GUI

    I couldn't recall why I needed to export or posted this article

    @anthonyx26 - I guesst trial and error! no harm in inserting when you can delete it easily

    ReplyDelete
  8. Have you seen and used Ola Hallengren's award winning solution?
    I use it across 18 Servers and it works well for us. Offcourse, test it on some Development Server in your environment before you roll out. You can obtain it from: http://ola.hallengren.com/downloads.html
    The MaintenanceSolution.sql script contains and creates all the Jobs. I modified it slightly for our environment, not to create the Backup Jobs, since I have already put in place our own custom backup solution.

    ReplyDelete