maintenance plan details

• Arpan Shinde

  SSC Enthusiast

  Points: 121

  More actions

  September 19, 2008 at 5:49 am

  #70763

  Hi All,

  Can anbody tell me how can we get maintenance plan details for scheduled backup, database integrity checks, reorg, reporting etc like...

  (I know we can get this through enterprise manager, But I want it by using any script or commands or any SP. Bcoz i want to create script where i can use this)

  1) Backup location

  2) Backup retention period

  3) backup scheduled time

  4) reorg is anable or not

  5) integrity checks enable or not

  6) tran log backup enable or not if yes then time , retetion period

  I know this is very hard to get but, if some get this i will be very greatfull.

  Thanks.

• MANU-J.

  SSC-Dedicated

  Points: 31126

  More actions

  September 19, 2008 at 12:19 pm

  #874408

  To giv it a start:

  select name as Job_Name,substring(command,charindex(' -',command),100) as Commandsd,sd.database_name

  from msdb..sysjobsteps js join msdb..sysdbmaintplan_jobs jp

  on

  js.job_id=jp.job_id

  join msdb.. sysjobs sj on

  js.job_id=sj.job_id

  join msdb..sysdbmaintplan_databases sd

  on sd.plan_id=jp.plan_id

  MJ