asked on

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.

Pawan Kumar

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ão

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?

Fred Webb

ASKER

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.

Fred Webb

ASKER

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ão

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.

Fred Webb

ASKER

yeah... I was afraid of that. Jobs are not as easy to modify as are Maintenance plans.

Vitor Montalvão

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.

Fred Webb

ASKER

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 Kumar

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

Fred Webb

ASKER

Pawan,
Nope still getting "Invalid length parameter passed to the RIGHT function."

Pawan Kumar

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

Fred Webb

ASKER

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 Kumar

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

Fred Webb

ASKER

Pawan,
Perfect, I knew it was filtered but I could find where. Where was the filter?

Pawan Kumar

I removed the where clause. If not working the change all joins to full outer joins.

Fred Webb

ASKER

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

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fred Webb

ASKER

Thanks for all the help

Pawan Kumar

welcome , glad to help.

Pawan Kumar

Question abandoned.
Provided verified solution.