Export/Import Maintenance Plans

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.
skull52Asked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
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

0
 
Pawan KumarDatabase ExpertCommented:
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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
skull52Author 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.
0
 
skull52Author 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.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
skull52Author Commented:
yeah... I was afraid of that. Jobs are not as easy to modify as are Maintenance plans.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
skull52Author 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

0
 
Pawan KumarDatabase ExpertCommented:
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

0
 
skull52Author Commented:
Pawan,
Nope still getting "Invalid length parameter passed to the RIGHT function."
0
 
Pawan KumarDatabase ExpertCommented:
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

0
 
skull52Author 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,
0
 
Pawan KumarDatabase ExpertCommented:
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

0
 
skull52Author Commented:
Pawan,
Perfect, I knew it was filtered but I could find where. Where was the filter?
0
 
Pawan KumarDatabase ExpertCommented:
I removed the where clause. If not working the change all joins to full outer joins.
0
 
skull52Author 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
0
 
skull52Author Commented:
Thanks for all the help
0
 
Pawan KumarDatabase ExpertCommented:
welcome , glad to help.
0
 
Pawan KumarDatabase ExpertCommented:
Question abandoned.
Provided verified solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.