Maintenance Plans on 2005

Question

Post reply

Maintenance Plans on 2005

gil pal

SSC Enthusiast

Points: 118
More actions
January 23, 2006 at 7:18 am

#167139

wasnt sure the question was clear so -
where dows SQLSERVER 2005 keeps the database name and tasks concerning maintanace Plans/Jobs.
for example :
I have a Maintanace Plan name TEST1 and a Job with the same name that works on TEST DB and does a Backup task. i want to be able to make a query that will bring back :
TEST1, TEST1, TEST, Backup.

thanks alot,
gil.

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

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

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 1

Maintenance plans are still stored in MSDB. Sysdbmaintplan_jobs contains the job_id linked to the maintenance plan.

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

gil pal SSC Enthusiast Points: 118 More actions · Answer 2

thanks for the reply but,

they kept the sysdbmaintplan_$$$ tables only for maintenance plans from earlier versions. new meaintenance plans are not kept on these tables.

you are right that on the old structure i can find all i want ( e.g. database name & tasks ). the problem is to find them in the new structure.

thanks, waiting for a solution,

Gil.

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 3

It looks like sysdtspackages90 contains a row for each maintenance plan. At least it does on my databases. I just have some test stuff and very littel else so it's pretty clean.

There is a packagetype column that indicates how the package was created.

http://msdn2.microsoft.com/en-us/library/ms181582.aspx

The jobs that are created are in sysjobs, etc.

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.