Export/Import Maintenance Plans

skull52
skull52 used Ask the Experts™
on
We are moving from SQL Server 2008R2 to SQL Server 2016 which is on a new server my dilemma is that i have 69 maintenance plans that I need to move to the new server. I know I can do it with SSIS by exporting modifying DTX files connection string for the new serve, but with 69 plans that is extremely tedious. Does anyone know of a tool or scripting that will accomplish this? I want to keep the as  Maintenance Plans not just Jobs.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Maintenance plans are SSIS packages which deploy under MSDB. So what we can do is go to (SQL Server 2008 R2) MSDB -> Maintenance plans  -> right click on the plan and say export and provide the path details and then we can connect to SQL Server 2016 and go to MSDB -> Maintenance plans  and we say import and provide the path of the saved Maintenance plan.

Sample steps are shown here.
https://mssqltrek.com/2011/08/27/how-to-transfercopy-maintenance-plans-from-one-server-to-other/

or you can try the powershell script from Microsoft
# MAINTENANCE PLANS
# import OK
$debugpreference = 2
$ImportFolder = "C:\"
#"
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ManagedDTS") | Out-Null
$application = New-Object Microsoft.SqlServer.Dts.Runtime.Application
[string[]]$DtsxFiles = Get-ChildItem -Path $ImportFolder -Filter "*.dtsx" -EA 0 | select -ExpandProperty FullName
if ($DtsxFiles) {
	$CountMP = 0
	foreach ($DtsxFile in $DtsxFiles) {
		try {
			$Package = $application.LoadPackage($DtsxFile, $Null)
			$MaintenancePlanName = [System.IO.Path]::GetFileNameWithoutExtension($DtsxFile)
			$application.SaveToSqlServerAs($Package, $Null, "Maintenance Plans\" + $MaintenancePlanName, '.', $Null, $Null)
			$CountMP++
		}
		catch {
			Write-Error "ERROR:" + $_.Exception.Message
		}
	}
	Write-Debug "$CountMP Maintenance Plan(s) imported from '$ImportFolder'"
}
else {
	Write-Warning "There is no Maintenance Plan"
}


# export OK
$debugpreference = 2
$ExportFolder = "C:\"
#"
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ManagedDTS") | Out-Null
$application = New-Object Microsoft.SqlServer.Dts.Runtime.Application
$PackageInfos = $application.GetPackageInfos("Maintenance Plans", ".", $Null, $Null)
if ($PackageInfos) {
	$CountMP = 0
	foreach ($PackageInfo in $PackageInfos) {
		$PackageName = $PackageInfo.Name
		$Package = $application.LoadFromSqlServer("\\Maintenance Plans\\" + $PackageName, ".", $Null, $Null, $Null)
		$Package.ProtectionLevel=0
		$file = Join-Path $ExportFolder ($PackageName + ".dtsx")
		$application.SaveToXml($file, $Package, $Null)
		$CountMP++
	}
	Write-Debug "$CountMP Maintenance Plan(s) exported to '$ExportFolder'"
}
else {
	Write-Warning "There is no Maintenance Plan"
}

Open in new window

https://social.technet.microsoft.com/Forums/Lync/en-US/52d71331-908d-4d5e-a14a-c665ce4cbc6a/exporting-sql-maintenance-plans?forum=sqltools
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I'm wondering why do you need 69 maintenance plans in a single SQL Server instance. Wouldn't this be an excellent opportunity to review them and maybe consolidate many of those maintenance plans?
skull52IT director

Author

Commented:
Vitor, unfortunately no, do to the nature of our business each one of these jobs has a specific function the majority of which are alerts when something goes wrong.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

skull52IT director

Author

Commented:
Pawan, Yes I know I can export/import the packages but it has to be done individually and the connection string in each package modified to the new server, this is what I wanted to avoid. I will try the powershell.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I don't think you have another option but migrate them one by one.
In all my years as DBA I never used maintenance plans. I would rather prefer to use T-SQL scripts in jobs that I can schedule. And it's much easy to migrate those jobs because they are stored in msdb database.
I also use to have a single SSIS server where I store the dtsx packages that need to be run for whatever SQL Server instance in the network. This way I have all them concentrated in a single server as they don't need to stay in the same SQL Server instance of the databases they are connecting to. Meaning that when you migrate a database you just need to update the dtsx package connection to point to the new location.
skull52IT director

Author

Commented:
yeah... I was afraid of that. Jobs are not as easy to modify as are Maintenance plans.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Jobs are not as easy to modify as are Maintenance plans.
Maintenance plans are very static. Jobs gives you a lot of flexibility. I personally do not use maintenance plans at all.
skull52IT director

Author

Commented:
Ok... I will look into that but how do I modify the SQL statements in a JOB?
On another note I am trying to get the Job Schedule information frequency of job running, day of the week, time, etc. I have tried the following code but when I comment out the "and smpld.line1 like '%Back Up%'" line i get the following error "Invalid length parameter passed to the RIGHT function."
/* 
Backup information from maintenance plans - jobs, schedules, etc.

Andy Galbraith @DBA_ANDY

MSSQL 2005+

Heavily borrows from http://sqlfool.com/2009/02/view-sql-agent-jobs-tsql/ 
for the original job schedule CTE and base query - thanks Michelle!

I modified Michelle's original query slightly and then added 
the maintenance plan information to match the jobs to their 
parent maintenance plans.

The filter that makes the query relevant to backup subplans is:

"and smpld.line1 like '%Back Up%'"

Commenting out or removing this line will display information about
all maintenance plan subplans and their enabled jobs
*/

Declare @weekDay Table 
(
    mask  int
    , maskValue varchar(32)
);

Insert Into @weekDay
    Select 1, 'Sunday'  UNION ALL
    Select 2, 'Monday'  UNION ALL
    Select 4, 'Tuesday'  UNION ALL
    Select 8, 'Wednesday'  UNION ALL
    Select 16, 'Thursday'  UNION ALL
    Select 32, 'Friday'  UNION ALL
    Select 64, 'Saturday';

With myCTE
As (
    Select sched.name As 'scheduleName'
    , sched.schedule_id
    , jobsched.job_id
    , Case 
        When sched.freq_type = 1 
            Then 'Once' 
        When sched.freq_type = 4 And sched.freq_interval = 1 
            Then 'Daily'
        When sched.freq_type = 4 
            Then 'Every ' + Cast(sched.freq_interval As varchar(5)) + ' days'
        When sched.freq_type = 8 
            Then Replace( Replace( Replace(( 
                Select maskValue 
                From @weekDay As x 
                Where sched.freq_interval & x.mask <> 0 
                Order By mask For XML Raw)
    , '"/><row maskValue="', ', '), '<row maskValue="', ''), '"/>', '') 
        + Case When sched.freq_recurrence_factor <> 0 
        And sched.freq_recurrence_factor = 1 
            Then '; weekly' 
    When sched.freq_recurrence_factor <> 0 
            Then '; every ' 
            + Cast(sched.freq_recurrence_factor As varchar(10)) + ' weeks' 
        End
        When sched.freq_type = 16 
            Then 'On day ' 
            + Cast(sched.freq_interval As varchar(10)) + ' of every '
            + Cast(sched.freq_recurrence_factor As varchar(10)) + ' months' 
        When sched.freq_type = 32 
            Then Case 
            When sched.freq_relative_interval = 1 
                Then 'First'
            When sched.freq_relative_interval = 2 
                Then 'Second'
            When sched.freq_relative_interval = 4 
                Then 'Third'
            When sched.freq_relative_interval = 8 
                Then 'Fourth'
            When sched.freq_relative_interval = 16 
                Then 'Last'
    End + 
    Case 
        When sched.freq_interval = 1 
            Then ' Sunday'
        When sched.freq_interval = 2 
            Then ' Monday'
        When sched.freq_interval = 3 
            Then ' Tuesday'
        When sched.freq_interval = 4 
            Then ' Wednesday'
        When sched.freq_interval = 5 
            Then ' Thursday'
        When sched.freq_interval = 6 
            Then ' Friday'
        When sched.freq_interval = 7 
            Then ' Saturday'
        When sched.freq_interval = 8 
            Then ' Day'
        When sched.freq_interval = 9 
            Then ' Weekday'
        When sched.freq_interval = 10 
            Then ' Weekend'
    End
    + 
    Case 
        When sched.freq_recurrence_factor <> 0 
        And sched.freq_recurrence_factor = 1 
            Then '; monthly'
        When sched.freq_recurrence_factor <> 0 
            Then '; every ' 
    + Cast(sched.freq_recurrence_factor As varchar(10)) + ' months' 
    End
    When sched.freq_type = 64 
        Then 'StartUp'
    When sched.freq_type = 128 
        Then 'Idle'
     End As 'frequency'
    , IsNull('Every ' + Cast(sched.freq_subday_interval As varchar(10)) + 
    Case 
        When sched.freq_subday_type = 2 
            Then ' seconds'
        When sched.freq_subday_type = 4 
            Then ' minutes'
        When sched.freq_subday_type = 8 
            Then ' hours'
    End, 'Once') As 'subFrequency'
    , Replicate('0', 6 - Len(sched.active_start_time)) 
        + Cast(sched.active_start_time As varchar(6)) As 'startTime'
    , Replicate('0', 6 - Len(sched.active_end_time)) 
        + Cast(sched.active_end_time As varchar(6)) As 'endTime'
    , Replicate('0', 6 - Len(jobsched.next_run_time)) 
        + Cast(jobsched.next_run_time As varchar(6)) As 'nextRunTime'
    , Cast(jobsched.next_run_date As char(8)) As 'nextRunDate'
    From msdb.dbo.sysschedules As sched
    Join msdb.dbo.sysjobschedules As jobsched
    On sched.schedule_id = jobsched.schedule_id
    Where sched.enabled = 1
)
Select DISTINCT p.name as 'Maintenance_Plan'
, p.[owner] as 'Plan_Owner'
, sp.subplan_name as 'Subplan_Name'
, smpld.line3 as 'Database_Names'
, RIGHT(smpld.line4,LEN(smpld.line4)-6) as 'Backup_Type'
, job.name As 'Job_Name'
, sched.frequency as 'Schedule_Frequency'
, sched.subFrequency as 'Schedule_Subfrequency'
, SubString(sched.startTime, 1, 2) + ':' 
    + SubString(sched.startTime, 3, 2) + ' - ' 
    + SubString(sched.endTime, 1, 2) + ':' 
    + SubString(sched.endTime, 3, 2) 
As 'Schedule_Time' -- HH:MM
, SubString(sched.nextRunDate, 1, 4) + '/' 
    + SubString(sched.nextRunDate, 5, 2) + '/' 
    + SubString(sched.nextRunDate, 7, 2) + ' ' 
    + SubString(sched.nextRunTime, 1, 2) + ':' 
    + SubString(sched.nextRunTime, 3, 2) 
As 'Next_Run_Date'
/* 
Note: the sysjobschedules table refreshes every 20 min, 
so Next_Run_Date may be out of date 
*/
From msdb.dbo.sysjobs As job
Join myCTE As sched
On job.job_id = sched.job_id
join  msdb.dbo.sysmaintplan_subplans sp
on sp.job_id = job.job_id
inner join msdb.dbo.sysmaintplan_plans p
on p.id = sp.plan_id
JOIN msdb.dbo.sysjobschedules sjs
ON job.job_id = sjs.job_id
INNER JOIN msdb.dbo.sysschedules ss
ON sjs.schedule_id = ss.schedule_id 
join msdb.dbo.sysmaintplan_log smpl
on p.id = smpl.plan_id 
and sp.subplan_id =smpl.subplan_id
join msdb.dbo.sysmaintplan_logdetail smpld
on smpl.task_detail_id=smpld.task_detail_id 
--and smpld.line1 like '%Back Up%'
where job.[enabled] = 1
and smpld.line3<>''
Order By Next_Run_Date;

Open in new window

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Please try this -

/* 
Backup information from maintenance plans - jobs, schedules, etc.

Andy Galbraith @DBA_ANDY

MSSQL 2005+

Heavily borrows from http://sqlfool.com/2009/02/view-sql-agent-jobs-tsql/ 
for the original job schedule CTE and base query - thanks Michelle!

I modified Michelle's original query slightly and then added 
the maintenance plan information to match the jobs to their 
parent maintenance plans.

The filter that makes the query relevant to backup subplans is:

"and smpld.line1 like '%Back Up%'"

Commenting out or removing this line will display information about
all maintenance plan subplans and their enabled jobs
*/

Declare @weekDay Table 
(
    mask  int
    , maskValue varchar(32)
);

Insert Into @weekDay
    Select 1, 'Sunday'  UNION ALL
    Select 2, 'Monday'  UNION ALL
    Select 4, 'Tuesday'  UNION ALL
    Select 8, 'Wednesday'  UNION ALL
    Select 16, 'Thursday'  UNION ALL
    Select 32, 'Friday'  UNION ALL
    Select 64, 'Saturday';

With myCTE
As (
    Select sched.name As 'scheduleName'
    , sched.schedule_id
    , jobsched.job_id
    , Case 
        When sched.freq_type = 1 
            Then 'Once' 
        When sched.freq_type = 4 And sched.freq_interval = 1 
            Then 'Daily'
        When sched.freq_type = 4 
            Then 'Every ' + Cast(sched.freq_interval As varchar(5)) + ' days'
        When sched.freq_type = 8 
            Then Replace( Replace( Replace(( 
                Select maskValue 
                From @weekDay As x 
                Where sched.freq_interval & x.mask <> 0 
                Order By mask For XML Raw)
    , '"/><row maskValue="', ', '), '<row maskValue="', ''), '"/>', '') 
        + Case When sched.freq_recurrence_factor <> 0 
        And sched.freq_recurrence_factor = 1 
            Then '; weekly' 
    When sched.freq_recurrence_factor <> 0 
            Then '; every ' 
            + Cast(sched.freq_recurrence_factor As varchar(10)) + ' weeks' 
        End
        When sched.freq_type = 16 
            Then 'On day ' 
            + Cast(sched.freq_interval As varchar(10)) + ' of every '
            + Cast(sched.freq_recurrence_factor As varchar(10)) + ' months' 
        When sched.freq_type = 32 
            Then Case 
            When sched.freq_relative_interval = 1 
                Then 'First'
            When sched.freq_relative_interval = 2 
                Then 'Second'
            When sched.freq_relative_interval = 4 
                Then 'Third'
            When sched.freq_relative_interval = 8 
                Then 'Fourth'
            When sched.freq_relative_interval = 16 
                Then 'Last'
    End + 
    Case 
        When sched.freq_interval = 1 
            Then ' Sunday'
        When sched.freq_interval = 2 
            Then ' Monday'
        When sched.freq_interval = 3 
            Then ' Tuesday'
        When sched.freq_interval = 4 
            Then ' Wednesday'
        When sched.freq_interval = 5 
            Then ' Thursday'
        When sched.freq_interval = 6 
            Then ' Friday'
        When sched.freq_interval = 7 
            Then ' Saturday'
        When sched.freq_interval = 8 
            Then ' Day'
        When sched.freq_interval = 9 
            Then ' Weekday'
        When sched.freq_interval = 10 
            Then ' Weekend'
    End
    + 
    Case 
        When sched.freq_recurrence_factor <> 0 
        And sched.freq_recurrence_factor = 1 
            Then '; monthly'
        When sched.freq_recurrence_factor <> 0 
            Then '; every ' 
    + Cast(sched.freq_recurrence_factor As varchar(10)) + ' months' 
    End
    When sched.freq_type = 64 
        Then 'StartUp'
    When sched.freq_type = 128 
        Then 'Idle'
     End As 'frequency'
    , IsNull('Every ' + Cast(sched.freq_subday_interval As varchar(10)) + 
    Case 
        When sched.freq_subday_type = 2 
            Then ' seconds'
        When sched.freq_subday_type = 4 
            Then ' minutes'
        When sched.freq_subday_type = 8 
            Then ' hours'
    End, 'Once') As 'subFrequency'
    , Replicate('0', 6 - Len(sched.active_start_time)) 
        + Cast(sched.active_start_time As varchar(6)) As 'startTime'
    , Replicate('0', 6 - Len(sched.active_end_time)) 
        + Cast(sched.active_end_time As varchar(6)) As 'endTime'
    , Replicate('0', 6 - Len(jobsched.next_run_time)) 
        + Cast(jobsched.next_run_time As varchar(6)) As 'nextRunTime'
    , Cast(jobsched.next_run_date As char(8)) As 'nextRunDate'
    From msdb.dbo.sysschedules As sched
    Join msdb.dbo.sysjobschedules As jobsched
    On sched.schedule_id = jobsched.schedule_id
    Where sched.enabled = 1
)
Select DISTINCT p.name as 'Maintenance_Plan'
, p.[owner] as 'Plan_Owner'
, sp.subplan_name as 'Subplan_Name'
, smpld.line3 as 'Database_Names'
, RIGHT(smpld.line4,LEN(smpld.line4)-6) as 'Backup_Type'
, job.name As 'Job_Name'
, sched.frequency as 'Schedule_Frequency'
, sched.subFrequency as 'Schedule_Subfrequency'
, SubString(sched.startTime, 1, 2) + ':' 
    + SubString(sched.startTime, 3, 2) + ' - ' 
    + SubString(sched.endTime, 1, 2) + ':' 
    + SubString(sched.endTime, 3, 2) 
As 'Schedule_Time' -- HH:MM
, SubString(sched.nextRunDate, 1, 4) + '/' 
    + SubString(sched.nextRunDate, 5, 2) + '/' 
    + SubString(sched.nextRunDate, 7, 2) + ' ' 
    + SubString(sched.nextRunTime, 1, 2) + ':' 
    + SubString(sched.nextRunTime, 3, 2) 
As 'Next_Run_Date'
/* 
Note: the sysjobschedules table refreshes every 20 min, 
so Next_Run_Date may be out of date 
*/
From msdb.dbo.sysjobs As job
Join myCTE As sched
On job.job_id = sched.job_id
join  msdb.dbo.sysmaintplan_subplans sp
on sp.job_id = job.job_id
inner join msdb.dbo.sysmaintplan_plans p
on p.id = sp.plan_id
JOIN msdb.dbo.sysjobschedules sjs
ON job.job_id = sjs.job_id
INNER JOIN msdb.dbo.sysschedules ss
ON sjs.schedule_id = ss.schedule_id 
join msdb.dbo.sysmaintplan_log smpl
on p.id = smpl.plan_id 
and sp.subplan_id =smpl.subplan_id
join msdb.dbo.sysmaintplan_logdetail smpld
on smpl.task_detail_id=smpld.task_detail_id 
and smpld.line1 like '%%'
where job.[enabled] = 1
and smpld.line3<>''
Order By Next_Run_Date;

Open in new window

skull52IT director

Author

Commented:
Pawan,
Nope still getting "Invalid length parameter passed to the RIGHT function."
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Please try this updated-

/* 
Backup information from maintenance plans - jobs, schedules, etc.

Andy Galbraith @DBA_ANDY

MSSQL 2005+

Heavily borrows from http://sqlfool.com/2009/02/view-sql-agent-jobs-tsql/ 
for the original job schedule CTE and base query - thanks Michelle!

I modified Michelle's original query slightly and then added 
the maintenance plan information to match the jobs to their 
parent maintenance plans.

The filter that makes the query relevant to backup subplans is:

"and smpld.line1 like '%Back Up%'"

Commenting out or removing this line will display information about
all maintenance plan subplans and their enabled jobs
*/

Declare @weekDay Table 
(
    mask  int
    , maskValue varchar(32)
);

Insert Into @weekDay
    Select 1, 'Sunday'  UNION ALL
    Select 2, 'Monday'  UNION ALL
    Select 4, 'Tuesday'  UNION ALL
    Select 8, 'Wednesday'  UNION ALL
    Select 16, 'Thursday'  UNION ALL
    Select 32, 'Friday'  UNION ALL
    Select 64, 'Saturday';

With myCTE
As (
    Select sched.name As 'scheduleName'
    , sched.schedule_id
    , jobsched.job_id
    , Case 
        When sched.freq_type = 1 
            Then 'Once' 
        When sched.freq_type = 4 And sched.freq_interval = 1 
            Then 'Daily'
        When sched.freq_type = 4 
            Then 'Every ' + Cast(sched.freq_interval As varchar(5)) + ' days'
        When sched.freq_type = 8 
            Then Replace( Replace( Replace(( 
                Select maskValue 
                From @weekDay As x 
                Where sched.freq_interval & x.mask <> 0 
                Order By mask For XML Raw)
    , '"/><row maskValue="', ', '), '<row maskValue="', ''), '"/>', '') 
        + Case When sched.freq_recurrence_factor <> 0 
        And sched.freq_recurrence_factor = 1 
            Then '; weekly' 
    When sched.freq_recurrence_factor <> 0 
            Then '; every ' 
            + Cast(sched.freq_recurrence_factor As varchar(10)) + ' weeks' 
        End
        When sched.freq_type = 16 
            Then 'On day ' 
            + Cast(sched.freq_interval As varchar(10)) + ' of every '
            + Cast(sched.freq_recurrence_factor As varchar(10)) + ' months' 
        When sched.freq_type = 32 
            Then Case 
            When sched.freq_relative_interval = 1 
                Then 'First'
            When sched.freq_relative_interval = 2 
                Then 'Second'
            When sched.freq_relative_interval = 4 
                Then 'Third'
            When sched.freq_relative_interval = 8 
                Then 'Fourth'
            When sched.freq_relative_interval = 16 
                Then 'Last'
    End + 
    Case 
        When sched.freq_interval = 1 
            Then ' Sunday'
        When sched.freq_interval = 2 
            Then ' Monday'
        When sched.freq_interval = 3 
            Then ' Tuesday'
        When sched.freq_interval = 4 
            Then ' Wednesday'
        When sched.freq_interval = 5 
            Then ' Thursday'
        When sched.freq_interval = 6 
            Then ' Friday'
        When sched.freq_interval = 7 
            Then ' Saturday'
        When sched.freq_interval = 8 
            Then ' Day'
        When sched.freq_interval = 9 
            Then ' Weekday'
        When sched.freq_interval = 10 
            Then ' Weekend'
    End
    + 
    Case 
        When sched.freq_recurrence_factor <> 0 
        And sched.freq_recurrence_factor = 1 
            Then '; monthly'
        When sched.freq_recurrence_factor <> 0 
            Then '; every ' 
    + Cast(sched.freq_recurrence_factor As varchar(10)) + ' months' 
    End
    When sched.freq_type = 64 
        Then 'StartUp'
    When sched.freq_type = 128 
        Then 'Idle'
     End As 'frequency'
    , IsNull('Every ' + Cast(sched.freq_subday_interval As varchar(10)) + 
    Case 
        When sched.freq_subday_type = 2 
            Then ' seconds'
        When sched.freq_subday_type = 4 
            Then ' minutes'
        When sched.freq_subday_type = 8 
            Then ' hours'
    End, 'Once') As 'subFrequency'
    , Replicate('0', 6 - Len(sched.active_start_time)) 
        + Cast(sched.active_start_time As varchar(6)) As 'startTime'
    , Replicate('0', 6 - Len(sched.active_end_time)) 
        + Cast(sched.active_end_time As varchar(6)) As 'endTime'
    , Replicate('0', 6 - Len(jobsched.next_run_time)) 
        + Cast(jobsched.next_run_time As varchar(6)) As 'nextRunTime'
    , Cast(jobsched.next_run_date As char(8)) As 'nextRunDate'
    From msdb.dbo.sysschedules As sched
    Join msdb.dbo.sysjobschedules As jobsched
    On sched.schedule_id = jobsched.schedule_id
    Where sched.enabled = 1
)
Select DISTINCT p.name as 'Maintenance_Plan'
, p.[owner] as 'Plan_Owner'
, sp.subplan_name as 'Subplan_Name'
, smpld.line3 as 'Database_Names'
, CASE WHEN LEN(smpld.line4)-6 > -1 THEN RIGHT(smpld.line4,LEN(smpld.line4)-6) ELSE '' END as 'Backup_Type'
, job.name As 'Job_Name'
, sched.frequency as 'Schedule_Frequency'
, sched.subFrequency as 'Schedule_Subfrequency'
, SubString(sched.startTime, 1, 2) + ':' 
    + SubString(sched.startTime, 3, 2) + ' - ' 
    + SubString(sched.endTime, 1, 2) + ':' 
    + SubString(sched.endTime, 3, 2) 
As 'Schedule_Time' -- HH:MM
, SubString(sched.nextRunDate, 1, 4) + '/' 
    + SubString(sched.nextRunDate, 5, 2) + '/' 
    + SubString(sched.nextRunDate, 7, 2) + ' ' 
    + SubString(sched.nextRunTime, 1, 2) + ':' 
    + SubString(sched.nextRunTime, 3, 2) 
As 'Next_Run_Date'
/* 
Note: the sysjobschedules table refreshes every 20 min, 
so Next_Run_Date may be out of date 
*/
From msdb.dbo.sysjobs As job
Join myCTE As sched
On job.job_id = sched.job_id
join  msdb.dbo.sysmaintplan_subplans sp
on sp.job_id = job.job_id
inner join msdb.dbo.sysmaintplan_plans p
on p.id = sp.plan_id
JOIN msdb.dbo.sysjobschedules sjs
ON job.job_id = sjs.job_id
INNER JOIN msdb.dbo.sysschedules ss
ON sjs.schedule_id = ss.schedule_id 
join msdb.dbo.sysmaintplan_log smpl
on p.id = smpl.plan_id 
and sp.subplan_id =smpl.subplan_id
join msdb.dbo.sysmaintplan_logdetail smpld
on smpl.task_detail_id=smpld.task_detail_id 
and smpld.line1 like '%%'
where job.[enabled] = 1
and smpld.line3<>''
Order By Next_Run_Date;

Open in new window

skull52IT director

Author

Commented:
Ok it didn't error but it is not displaying all the Jobs, only 11 out of 70, and they seem to be admin jobs,
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Data is filtered. Please try this -

/* 
Backup information from maintenance plans - jobs, schedules, etc.

Andy Galbraith @DBA_ANDY

MSSQL 2005+

Heavily borrows from http://sqlfool.com/2009/02/view-sql-agent-jobs-tsql/ 
for the original job schedule CTE and base query - thanks Michelle!

I modified Michelle's original query slightly and then added 
the maintenance plan information to match the jobs to their 
parent maintenance plans.

The filter that makes the query relevant to backup subplans is:

"and smpld.line1 like '%Back Up%'"

Commenting out or removing this line will display information about
all maintenance plan subplans and their enabled jobs
*/

Declare @weekDay Table 
(
    mask  int
    , maskValue varchar(32)
);

Insert Into @weekDay
    Select 1, 'Sunday'  UNION ALL
    Select 2, 'Monday'  UNION ALL
    Select 4, 'Tuesday'  UNION ALL
    Select 8, 'Wednesday'  UNION ALL
    Select 16, 'Thursday'  UNION ALL
    Select 32, 'Friday'  UNION ALL
    Select 64, 'Saturday';

With myCTE
As (
    Select sched.name As 'scheduleName'
    , sched.schedule_id
    , jobsched.job_id
    , Case 
        When sched.freq_type = 1 
            Then 'Once' 
        When sched.freq_type = 4 And sched.freq_interval = 1 
            Then 'Daily'
        When sched.freq_type = 4 
            Then 'Every ' + Cast(sched.freq_interval As varchar(5)) + ' days'
        When sched.freq_type = 8 
            Then Replace( Replace( Replace(( 
                Select maskValue 
                From @weekDay As x 
                Where sched.freq_interval & x.mask <> 0 
                Order By mask For XML Raw)
    , '"/><row maskValue="', ', '), '<row maskValue="', ''), '"/>', '') 
        + Case When sched.freq_recurrence_factor <> 0 
        And sched.freq_recurrence_factor = 1 
            Then '; weekly' 
    When sched.freq_recurrence_factor <> 0 
            Then '; every ' 
            + Cast(sched.freq_recurrence_factor As varchar(10)) + ' weeks' 
        End
        When sched.freq_type = 16 
            Then 'On day ' 
            + Cast(sched.freq_interval As varchar(10)) + ' of every '
            + Cast(sched.freq_recurrence_factor As varchar(10)) + ' months' 
        When sched.freq_type = 32 
            Then Case 
            When sched.freq_relative_interval = 1 
                Then 'First'
            When sched.freq_relative_interval = 2 
                Then 'Second'
            When sched.freq_relative_interval = 4 
                Then 'Third'
            When sched.freq_relative_interval = 8 
                Then 'Fourth'
            When sched.freq_relative_interval = 16 
                Then 'Last'
    End + 
    Case 
        When sched.freq_interval = 1 
            Then ' Sunday'
        When sched.freq_interval = 2 
            Then ' Monday'
        When sched.freq_interval = 3 
            Then ' Tuesday'
        When sched.freq_interval = 4 
            Then ' Wednesday'
        When sched.freq_interval = 5 
            Then ' Thursday'
        When sched.freq_interval = 6 
            Then ' Friday'
        When sched.freq_interval = 7 
            Then ' Saturday'
        When sched.freq_interval = 8 
            Then ' Day'
        When sched.freq_interval = 9 
            Then ' Weekday'
        When sched.freq_interval = 10 
            Then ' Weekend'
    End
    + 
    Case 
        When sched.freq_recurrence_factor <> 0 
        And sched.freq_recurrence_factor = 1 
            Then '; monthly'
        When sched.freq_recurrence_factor <> 0 
            Then '; every ' 
    + Cast(sched.freq_recurrence_factor As varchar(10)) + ' months' 
    End
    When sched.freq_type = 64 
        Then 'StartUp'
    When sched.freq_type = 128 
        Then 'Idle'
     End As 'frequency'
    , IsNull('Every ' + Cast(sched.freq_subday_interval As varchar(10)) + 
    Case 
        When sched.freq_subday_type = 2 
            Then ' seconds'
        When sched.freq_subday_type = 4 
            Then ' minutes'
        When sched.freq_subday_type = 8 
            Then ' hours'
    End, 'Once') As 'subFrequency'
    , Replicate('0', 6 - Len(sched.active_start_time)) 
        + Cast(sched.active_start_time As varchar(6)) As 'startTime'
    , Replicate('0', 6 - Len(sched.active_end_time)) 
        + Cast(sched.active_end_time As varchar(6)) As 'endTime'
    , Replicate('0', 6 - Len(jobsched.next_run_time)) 
        + Cast(jobsched.next_run_time As varchar(6)) As 'nextRunTime'
    , Cast(jobsched.next_run_date As char(8)) As 'nextRunDate'
    From msdb.dbo.sysschedules As sched
    Join msdb.dbo.sysjobschedules As jobsched
    On sched.schedule_id = jobsched.schedule_id
    Where sched.enabled = 1
)
Select DISTINCT p.name as 'Maintenance_Plan'
, p.[owner] as 'Plan_Owner'
, sp.subplan_name as 'Subplan_Name'
, smpld.line3 as 'Database_Names'
, CASE WHEN LEN(smpld.line4)-6 > -1 THEN RIGHT(smpld.line4,LEN(smpld.line4)-6) ELSE '' END as 'Backup_Type'
, job.name As 'Job_Name'
, sched.frequency as 'Schedule_Frequency'
, sched.subFrequency as 'Schedule_Subfrequency'
, SubString(sched.startTime, 1, 2) + ':' 
    + SubString(sched.startTime, 3, 2) + ' - ' 
    + SubString(sched.endTime, 1, 2) + ':' 
    + SubString(sched.endTime, 3, 2) 
As 'Schedule_Time' -- HH:MM
, SubString(sched.nextRunDate, 1, 4) + '/' 
    + SubString(sched.nextRunDate, 5, 2) + '/' 
    + SubString(sched.nextRunDate, 7, 2) + ' ' 
    + SubString(sched.nextRunTime, 1, 2) + ':' 
    + SubString(sched.nextRunTime, 3, 2) 
As 'Next_Run_Date'
/* 
Note: the sysjobschedules table refreshes every 20 min, 
so Next_Run_Date may be out of date 
*/
From msdb.dbo.sysjobs As job
Join myCTE As sched
On job.job_id = sched.job_id
join  msdb.dbo.sysmaintplan_subplans sp
on sp.job_id = job.job_id
inner join msdb.dbo.sysmaintplan_plans p
on p.id = sp.plan_id
JOIN msdb.dbo.sysjobschedules sjs
ON job.job_id = sjs.job_id
INNER JOIN msdb.dbo.sysschedules ss
ON sjs.schedule_id = ss.schedule_id 
join msdb.dbo.sysmaintplan_log smpl
on p.id = smpl.plan_id 
and sp.subplan_id =smpl.subplan_id
join msdb.dbo.sysmaintplan_logdetail smpld
on smpl.task_detail_id=smpld.task_detail_id 
and smpld.line1 like '%%'
Order By Next_Run_Date;

Open in new window

skull52IT director

Author

Commented:
Pawan,
Perfect, I knew it was filtered but I could find where. Where was the filter?
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
I removed the where clause. If not working the change all joins to full outer joins.
skull52IT director

Author

Commented:
Ok... Almost there It seems to be only display Maintenance plans and only those enabled i removed the  "Where sched.enabled = 1" clause but that had no effect
Database Expert
Awarded 2016
Top Expert 2016
Commented:
This will help -

/* 
Backup information from maintenance plans - jobs, schedules, etc.

Andy Galbraith @DBA_ANDY

MSSQL 2005+

Heavily borrows from http://sqlfool.com/2009/02/view-sql-agent-jobs-tsql/ 
for the original job schedule CTE and base query - thanks Michelle!

I modified Michelle's original query slightly and then added 
the maintenance plan information to match the jobs to their 
parent maintenance plans.

The filter that makes the query relevant to backup subplans is:

"and smpld.line1 like '%Back Up%'"

Commenting out or removing this line will display information about
all maintenance plan subplans and their enabled jobs
*/

Declare @weekDay Table 
(
    mask  int
    , maskValue varchar(32)
);

Insert Into @weekDay
    Select 1, 'Sunday'  UNION ALL
    Select 2, 'Monday'  UNION ALL
    Select 4, 'Tuesday'  UNION ALL
    Select 8, 'Wednesday'  UNION ALL
    Select 16, 'Thursday'  UNION ALL
    Select 32, 'Friday'  UNION ALL
    Select 64, 'Saturday';

With myCTE
As (
    Select sched.name As 'scheduleName'
    , sched.schedule_id
    , jobsched.job_id
    , Case 
        When sched.freq_type = 1 
            Then 'Once' 
        When sched.freq_type = 4 And sched.freq_interval = 1 
            Then 'Daily'
        When sched.freq_type = 4 
            Then 'Every ' + Cast(sched.freq_interval As varchar(5)) + ' days'
        When sched.freq_type = 8 
            Then Replace( Replace( Replace(( 
                Select maskValue 
                From @weekDay As x 
                Where sched.freq_interval & x.mask <> 0 
                Order By mask For XML Raw)
    , '"/><row maskValue="', ', '), '<row maskValue="', ''), '"/>', '') 
        + Case When sched.freq_recurrence_factor <> 0 
        And sched.freq_recurrence_factor = 1 
            Then '; weekly' 
    When sched.freq_recurrence_factor <> 0 
            Then '; every ' 
            + Cast(sched.freq_recurrence_factor As varchar(10)) + ' weeks' 
        End
        When sched.freq_type = 16 
            Then 'On day ' 
            + Cast(sched.freq_interval As varchar(10)) + ' of every '
            + Cast(sched.freq_recurrence_factor As varchar(10)) + ' months' 
        When sched.freq_type = 32 
            Then Case 
            When sched.freq_relative_interval = 1 
                Then 'First'
            When sched.freq_relative_interval = 2 
                Then 'Second'
            When sched.freq_relative_interval = 4 
                Then 'Third'
            When sched.freq_relative_interval = 8 
                Then 'Fourth'
            When sched.freq_relative_interval = 16 
                Then 'Last'
    End + 
    Case 
        When sched.freq_interval = 1 
            Then ' Sunday'
        When sched.freq_interval = 2 
            Then ' Monday'
        When sched.freq_interval = 3 
            Then ' Tuesday'
        When sched.freq_interval = 4 
            Then ' Wednesday'
        When sched.freq_interval = 5 
            Then ' Thursday'
        When sched.freq_interval = 6 
            Then ' Friday'
        When sched.freq_interval = 7 
            Then ' Saturday'
        When sched.freq_interval = 8 
            Then ' Day'
        When sched.freq_interval = 9 
            Then ' Weekday'
        When sched.freq_interval = 10 
            Then ' Weekend'
    End
    + 
    Case 
        When sched.freq_recurrence_factor <> 0 
        And sched.freq_recurrence_factor = 1 
            Then '; monthly'
        When sched.freq_recurrence_factor <> 0 
            Then '; every ' 
    + Cast(sched.freq_recurrence_factor As varchar(10)) + ' months' 
    End
    When sched.freq_type = 64 
        Then 'StartUp'
    When sched.freq_type = 128 
        Then 'Idle'
     End As 'frequency'
    , IsNull('Every ' + Cast(sched.freq_subday_interval As varchar(10)) + 
    Case 
        When sched.freq_subday_type = 2 
            Then ' seconds'
        When sched.freq_subday_type = 4 
            Then ' minutes'
        When sched.freq_subday_type = 8 
            Then ' hours'
    End, 'Once') As 'subFrequency'
    , Replicate('0', 6 - Len(sched.active_start_time)) 
        + Cast(sched.active_start_time As varchar(6)) As 'startTime'
    , Replicate('0', 6 - Len(sched.active_end_time)) 
        + Cast(sched.active_end_time As varchar(6)) As 'endTime'
    , Replicate('0', 6 - Len(jobsched.next_run_time)) 
        + Cast(jobsched.next_run_time As varchar(6)) As 'nextRunTime'
    , Cast(jobsched.next_run_date As char(8)) As 'nextRunDate'
    From msdb.dbo.sysschedules As sched
    FULL OUTER JOIN msdb.dbo.sysjobschedules As jobsched
    On sched.schedule_id = jobsched.schedule_id
    Where sched.enabled = 1
)
Select DISTINCT p.name as 'Maintenance_Plan'
, p.[owner] as 'Plan_Owner'
, sp.subplan_name as 'Subplan_Name'
, smpld.line3 as 'Database_Names'
, CASE WHEN LEN(smpld.line4)-6 > -1 THEN RIGHT(smpld.line4,LEN(smpld.line4)-6) ELSE '' END as 'Backup_Type'
, job.name As 'Job_Name'
, sched.frequency as 'Schedule_Frequency'
, sched.subFrequency as 'Schedule_Subfrequency'
, SubString(sched.startTime, 1, 2) + ':' 
    + SubString(sched.startTime, 3, 2) + ' - ' 
    + SubString(sched.endTime, 1, 2) + ':' 
    + SubString(sched.endTime, 3, 2) 
As 'Schedule_Time' -- HH:MM
, SubString(sched.nextRunDate, 1, 4) + '/' 
    + SubString(sched.nextRunDate, 5, 2) + '/' 
    + SubString(sched.nextRunDate, 7, 2) + ' ' 
    + SubString(sched.nextRunTime, 1, 2) + ':' 
    + SubString(sched.nextRunTime, 3, 2) 
As 'Next_Run_Date'
/* 
Note: the sysjobschedules table refreshes every 20 min, 
so Next_Run_Date may be out of date 
*/
From msdb.dbo.sysjobs As job
FULL OUTER JOIN myCTE As sched
On job.job_id = sched.job_id
FULL OUTER JOIN  msdb.dbo.sysmaintplan_subplans sp
on sp.job_id = job.job_id
FULL OUTER JOIN msdb.dbo.sysmaintplan_plans p
on p.id = sp.plan_id
FULL OUTER JOIN msdb.dbo.sysjobschedules sjs
ON job.job_id = sjs.job_id
FULL OUTER JOIN msdb.dbo.sysschedules ss
ON sjs.schedule_id = ss.schedule_id 
FULL OUTER JOIN msdb.dbo.sysmaintplan_log smpl
on p.id = smpl.plan_id 
and sp.subplan_id =smpl.subplan_id
FULL OUTER JOIN msdb.dbo.sysmaintplan_logdetail smpld
on smpl.task_detail_id=smpld.task_detail_id 
and smpld.line1 like '%%'
Order By Next_Run_Date;

Open in new window

skull52IT director

Author

Commented:
Thanks for all the help
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
welcome , glad to help.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Question abandoned.
Provided verified solution.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial