locked
Importing SQL Server 2005 Maintenance Plans Into SQL Server 2008 RRS feed

  • Question

  • I am migrating to SQL Server 2008.  I have exported my maintenance plans from my 2005 boxes and imported them into a2008 box; however when I test them they show a successful run but they are not actually working.  I am running SS 2008 Enterprise X64 SP1.  Is it possible to run maintenance plans from SS 2005 on SS 2008?  Should I just re-create the maintenance plans in SS 2008?

    Thanks,



    Thursday, June 4, 2009 3:12 PM

Answers

  • I am going off of memory here, and haven't tested this lately.

    There are two main paths of upgrade to consider.
    1. Upgrading (migrating) from a separate SQL Server 2005 instance to a SQL Server 2008 instance (your scenario). Migrating MSDB database to the new 2008 instance isn't an automated scenario - you can't restore MSDB between versions of sql. There isn't a wizard to "copy all jobs & maintenance plans" that I am aware of. You can script jobs by right clicking on them, but I don't think there is an equivalent to script out maintenance plans.

    2. Upgrading in place - existing SQL server 2005 upgraded to SQL Server 2008 (installed on top) of the 2005 instance such that MSDB will be upgraded in place. The maintenance plans will be upgraded too I hope.


    Maintenance plans are stored with two kinds of information in the MSDB database
    A. an SSIS package (stored in msdb tables sysdtspackages90 in 2005 / sysssispackages in 2008)
    B. a set of metadata stored in tables to remember the plan and the subplans in each design
    {
    select * from msdb.dbo.sysmaintplan_plans
    select * from msdb.dbo.sysmaintplan_subplans
    select * from msdb.dbo.sysmaintplan_log
    }


    To copy the package, you have to export the packages, upgrade them to 2008, and import them in 2008, which is relatively easy, but copying the metadata to match isn't automatic. I don't know if it even works, because I haven't tested yet.

    The metadata portion is harder. There is trouble sometimes when the metadata doesn't match the package design. Some of this was fixed in SP2 of 2005, but I haven't tried the 2005 -> 2008 scenario you mention http://support.microsoft.com/?id=922651   While you could probably copy the rows from one MSDB to the other MSDB, I'm not sure if the format of these tables has changed between versions, and if the numbers/names are exactly the same or not. It sounds unsupported to do that copy, but you might be willing to test it to see if it just works.

    If you were to move the maintenance plans ssis packages, references to the server names and databases in the "local connection" connection manager inside the maintenance plan will not be updated from what I remember, so it may point to your original server after the move, which does little good to maintain the new server. If they have the same netbios network name, its probably finding the right server, but if the server names are different, these packages would have to be edited to point to the new server name.

    There is not wizard or simple way to automate copying maintenance plans between machines. You are hoping to do that, plus upgrade from 2005 -> 2008 will involve the SSIS package getting upgraded to refer to the new 2008 components.

    I have talked with the Management Tools folks on the SQL team at Microsoft before and filed an Ask for a feature to do this, so they are aware of the issue and hopefully will include this on the list of features in the next product. The SSIS product group team doesn't directly own maintenance plans, so while they have half the puzzle on how maintenance plans are implemented as SSIS pacakages, the other half is in a different team who owns the management studio experience.

    There have been suggestions filed to ask for a feature to copy maintenance plans...
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=178812&wa=wsignin1.0
    There is a way to do this using SMO I suspect, but I haven't found a good code sample to do so. The "attachment" on the connect item is no longer attached.

    If your maintenance plans are simple, I'd say for now you are better offer recreating it due to the complexities of metadata being stored, and the server names being wrong.

    If your maintenance plans are complex, you might try the export of the SSIS package, and then upgrade the packages by opening in BIDs 2008 once or with the package upgrade utilitity, and then save into MSDB with server storage again. 

    Thanks, Jason
    Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance
    • Proposed as answer by Matt Masson Wednesday, July 8, 2009 2:12 PM
    • Marked as answer by Bob Bojanic Thursday, July 9, 2009 6:28 PM
    • Unmarked as answer by thecoleman Friday, July 31, 2009 3:35 PM
    • Marked as answer by thecoleman Friday, July 31, 2009 3:35 PM
    Saturday, July 4, 2009 1:44 PM