July 9, 2002 at 3:06 pm
Hi, I'm trying to write a script that will list each maintenance plan with details. I know how to make it display all the maintenance plans now, but I want it to be able to display things about the maintenance plan like the directory the backup will go to, and the time period to be run.
July 9, 2002 at 9:34 pm
Unfortunately, you're going to have to go several places to get this information.
The basic information on the maintenance plans are stored in the following tables of the msdb database:
These tables are for the maintenance plans, which databases they run against, and what jobs they are associated with.
However, a lot of the information are stored in the respective jobs themselves. You can query the sysjobsteps table, also in msdb. The job steps have the flags for the xp_sqlmaint (basically the sqlmaint utility) that correspond with what was toggled in the maintenance plan. For a list fo thes witches and what they mean, here is the MSDN Books Online for SQL Server 2000's page on the sqlmaint utility:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_sqlmaint_19ix.asp
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
July 9, 2002 at 9:56 pm
The best way to see what is stored where is to profile while you create/edit a maint plan. As far as I can tell the only place where the backup folder for the plan is stored is in the job itself.
Andy
July 9, 2002 at 10:20 pm
It stores it in the job step itself. For example:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 40513CD1-A02E-4806-B866-5B67895C747A -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP" -DelBkUps 4WEEKS -CrBkSubDir -BkExt "BAK"'
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
July 10, 2002 at 9:42 am
If you get this, pls post it. We have mostly standard, but a few different ones and this would be nice.
Steve Jones
July 10, 2002 at 10:00 am
By using the xp_sqlmaint procedure, that just calls the sqlmaint utility which performs database maintenance operations. I want to write a script that will be able to view all the maintenance plans and their details like what time it will run, how often and where it will be stored.
July 10, 2002 at 10:17 am
This is going to take a bit of work and it'll involve collecting information from system tables and also some string parsing, as I've indicated. Would be a good topic for an article.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply