Post reply

SQL Server 2000: How to copy database maintenance plans from one server to another

  • August 21, 2007 at 10:25 am

    #178136

    Does anyone know how to copy database maintenance plans (not just the underlying jobs) from one server to another ?

  • August 21, 2007 at 12:18 pm

    #728402

    Hi,

    In SQL 2000 you can use the Data Transformation Task  and create a package with the transform job task and I think all the jobs created by Maintenance plan can be transfered from one server to another (source to destination)

     

    Asiff

  • August 21, 2007 at 12:25 pm

    #728403

    I don't think the Maintenance Plan (ie: job metadata) gets transferred with it's job(s) though..

  • August 22, 2007 at 12:07 pm

    #728672

    I think that you can. Look at the three tables in the msdb.

    sysjobs, sysdbmaintplan_jobs, and sysdbmaintplans.

    Join these tables with necessary criteria and export to your target server.

    An example of snippet is:

    SELECT * FROM sysjobs sj, sysdbmaintplan_jobs sdj, sysdbmaintplans sdp

    WHERE sj.job_id = sdj.job_id AND sdj.plan_id = sdp.plan_id AND plan_name = 'DB Maintenance Plan1'

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply