The best way that I have employed is to restore the MSDB database. That will get you all the jobs, maintenance plans and their histories and also backup set details.
However if you say that you don’t want anything except Maintenance plan only then here is the method. Don’t forget that there are jobs attached to maintenance plan as well. So I would import the data from following tables.
sysdbmaintplan_databases - Contains one row for each database that has an associated maintenance plan. This table is stored in the msdb database.
sysdbmaintplan_history - Contains one row for each maintenance plan action performed. This table is stored in the msdb database.
sysdbmaintplan_jobs - Contains one row for each maintenance plan job. This table is stored in the msdb database.
sysdbmaintplans - Contains one row for each database maintenance plan. This table is stored in the msdb database.
For jobs following tables need to be copied/ imported.
sysjobs - Stores the information for each scheduled job to be executed by SQL Server Agent. This table is stored in the msdb database.
sysjobschedules - Contains schedule information for jobs to be executed by SQL Server Agent. This table is stored in the msdb database.
sysjobsteps - Contains the information for each step in a job to be executed by SQL Server Agent. This table is stored in the msdb database.
NOTE: Table definitions are as given in the books online (help files)
So basically we have to move data from following queries to respective tables.
SELECT * FROM sysdbmaintplans
SELECT * FROM sysjobs WHERE job_id in (SELECT job_id FROM sysdbmaintplan_jobs)
SELECT * FROM sysjobsteps WHERE job_id in (SELECT job_id FROM sysdbmaintplan_jobs)
SELECT * FROM sysjobschedules WHERE job_id in (SELECT job_id FROM sysdbmaintplan_jobs)
Table names are self explained.
Similarly DTS packages can also be moved. Check it out here http://www.sqldts.com/204.aspx
No comments:
Post a Comment