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.
skull52IT director Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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.
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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.
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
skull52IT director Author 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
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

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
skull52IT director Author 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
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,
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
skull52IT director Author 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
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
0
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
skull52IT director Author Commented:
Thanks for all the help
0
Pawan KumarDatabase ExpertCommented:
welcome , glad to help.
0
Pawan KumarDatabase ExpertCommented:
Question abandoned.
Provided verified solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.