Scripting out DB Maint Plan Jobs

  • I scripted out the jobs included in a DB maint plan (for emergency use in future). Ran the scripts on another server to test and they were created successfully. Problem is the DB maint plan was not created, just the jobs. Why did the DB maint plan not show up?

    Any ideas? Should I be concerned - just leave the jobs there and not worry about no DB maint plan? (I'm not good at scripting yet so relying on the DB maint plans initially.)

    GailG

     


    Kindest Regards,

    Gail G

  • The jobs moved to a new server without the maintenance plan will not work.  If you look at the first step in the maintenance plan job you will see something like:

     "EXECUTE master.dbo.xp_sqlmaint N'-PlanID FB5066E0-93B1-49E7-9A81-A3CFB879173D -Rpt"

    This is a unique id of the maintenance plan on the originating server.  Without the exact matching plan the job will fail.  The easiest way to move these is to move the system databases (master, model, msdb) as well to ensure all login, users and jobs are synched.  If this is not an option, or symply overkill I would simply recreate the maintenance plan on the new server.  Or better yet you can set up a master...slave server configuration where the maintenance plan can reside on one central master server and then enlist slave servers to execute the job on.

    Good Luck

  • These jobs (if there were a disaster) would only be run on the original server. System DBs could be restored if needed. The DB maint plan would be there then and these jobs would be fine. I did see the plan id.

    My goal here was simply to script off all items that would help restore if a disaster occurred or system crash, etc. In many DBA books, it talks about running sqldiag.exe, sp_configure, and other tasks that are important when documenting your application.

    You answered my question, thanks. New job - trying to start out on the right foot....


    Kindest Regards,

    Gail G

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

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