The following T-SQL script will generate a backup schedule report from transaction log and data backup plans as well as SQL Lightspeed jobs.Returning the following:

Plans/jobs with multiple schedules are returned for every schedule. Type of plan/job is determined by searching msdb.dbo.sysjobsteps.command that contain the following:

• -BkUpDB - SQL Maint Database Backup
• -BkUpLog - SQL Maint Log Backup
• xp_backup_database - SQL Lightspeed Database Backup
• xp_backup_log - SQL Lightspeed Log Backup

Average duration is calculated from job history (if any exists). Job run times less than 15 seconds are excluded to avoid short run times from skewing average duration calculations. This can happen for various reasons like backup software holding file locks on your backup files or maybe you forgot to put a tape in the tape drive.

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES
		WHERE ROUTINE_NAME = 'fn_AgentTime2Bigint')
	DROP FUNCTION fn_AgentTime2Bigint
GO

CREATE FUNCTION fn_AgentTime2Bigint
(
	@agenttime int
)
RETURNS bigint
AS
BEGIN
    DECLARE @date datetime,
            @hour int,
            @min int,
            @sec int,
            @datestr nvarchar(40)

    select @hour = (@agenttime / 10000)
    select @min = (@agenttime - (@hour * 10000)) / 100
    select @sec = (@agenttime - (@hour * 10000) - (@min * 100))
    return @hour * 3600 + @min * 60 + @sec
end
GO

CREATE TABLE #tmp_backup_plans (plan_id sysname
	, type varchar(255), maintcommand nvarchar(3200)
	, job_id sysname, avg_duration varchar(255))

-- Get all data and log plans
INSERT INTO #tmp_backup_plans
SELECT DISTINCT plans.plan_id
	,CASE
		WHEN CHARINDEX ('-BkUpDB',steps.command) > 0 THEN 'Data'
		WHEN CHARINDEX ('-BkUpLog',steps.command) > 0 THEN 'Log'
	END, command,
	jobs.job_id, '0:00:00'
FROM msdb.dbo.sysdbmaintplans plans
	INNER JOIN msdb.dbo.sysdbmaintplan_jobs jobs ON
		plans.plan_id = jobs.plan_id
	INNER JOIN msdb.dbo.sysjobs job ON
		jobs.job_id = job.job_id
	INNER JOIN msdb.dbo.sysjobsteps steps ON
		job.job_id = steps.job_id
WHERE plans.plan_id != '{00000000-0000-0000-0000-000000000000}'
	AND (CHARINDEX ('-BkUpDB',steps.command) > 0
		OR CHARINDEX ('-BkUpLog',steps.command) > 0)

SELECT job_id
	, CASE
		WHEN avg_seconds < 60 THEN '0:01:00'
		ELSE CONVERT(varchar,DATEADD(ss,avg_seconds,'1/1/1900'),108)
	END AS avg_duration
INTO #tmp_backup_durations
FROM	(
	SELECT job_id, AVG(seconds) AS avg_seconds
	FROM (
		SELECT history.job_id, run_date, run_time
			, SUM(dbo.fn_AgentTime2Bigint(run_duration)) AS seconds
		FROM #tmp_backup_plans
			INNER JOIN msdb.dbo.sysjobhistory history ON
				#tmp_backup_plans.job_id = history.job_id
		GROUP BY history.job_id, run_date, run_time
		HAVING SUM(dbo.fn_AgentTime2Bigint(run_duration)) > 15
		) times
	GROUP BY job_id
	) avg_times


UPDATE #tmp_backup_plans
SET avg_duration = times.avg_duration
FROM #tmp_backup_durations times
	INNER JOIN #tmp_backup_plans plans ON
		times.job_id = plans.job_id


-- Lightspeed jobs
CREATE TABLE #tmp_sls_jobs (job_id sysname
		, type varchar(255)
		, maintcommand nvarchar(3200)
		, avg_duration varchar(255))


INSERT INTO #tmp_sls_jobs
SELECT DISTINCT jobs.job_id
	,CASE
		WHEN CHARINDEX ('xp_backup_database',steps.command) > 0 THEN 'Data'
		WHEN CHARINDEX ('xp_backup_log',steps.command) > 0 THEN 'Log'
	END, command, '0:00:00'
FROM msdb..sysjobs jobs
	INNER JOIN msdb..sysjobsteps steps ON
		jobs.job_id = steps.job_id
WHERE 	(steps.command LIKE '%xp_backup_database%'
	OR
	steps.command LIKE '%xp_backup_log%')
	AND jobs.enabled = 1
	

SELECT job_id
	, CASE
		WHEN avg_seconds < 60 THEN '0:01:00'
		ELSE CONVERT(varchar,DATEADD(ss,avg_seconds,'1/1/1900'),108)
	END AS avg_duration
INTO #tmp_sls_durations
FROM	(
	SELECT job_id, AVG(seconds) AS avg_seconds
	FROM (
		SELECT history.job_id, run_date, run_time
			, SUM(dbo.fn_AgentTime2Bigint(run_duration)) AS seconds
		FROM #tmp_sls_jobs
			INNER JOIN msdb.dbo.sysjobhistory history ON
				#tmp_sls_jobs.job_id = history.job_id
		GROUP BY history.job_id, run_date, run_time
		HAVING SUM(dbo.fn_AgentTime2Bigint(run_duration)) > 15
		) times
	GROUP BY job_id
	) avg_times


-- Update durations
UPDATE #tmp_sls_jobs
SET avg_duration = times.avg_duration
FROM #tmp_sls_durations times
	INNER JOIN #tmp_sls_jobs slsjobs ON
		times.job_id = slsjobs.job_id




-- Plan schedule(s)
SELECT 	plans.plan_id, plans.plan_name, #tmp_backup_plans.type, 
	job.job_id
	,CASE schedule.freq_type
		WHEN 1 THEN 'Run Once'
		WHEN 4 THEN 'Daily'
		WHEN 8 THEN 'Every'
			+ CASE freq_interval & 2 WHEN 2 THEN ' Monday' ELSE '' END
			+ CASE freq_interval & 4 WHEN 4 THEN ' Tuesday' ELSE '' END
			+ CASE freq_interval & 8 WHEN 8 THEN ' Wednesday' ELSE '' END
			+ CASE freq_interval & 16 WHEN 16 THEN ' Thursday' ELSE '' END
			+ CASE freq_interval & 32 WHEN 32 THEN ' Friday' ELSE '' END
			+ CASE freq_interval & 64 WHEN 64 THEN ' Saturday' ELSE '' END
			+ CASE freq_interval & 1 WHEN 1 THEN ' Sunday' ELSE '' END
		WHEN 16 THEN 'Monthly on day ' + CONVERT(varchar(2), freq_interval)
		WHEN 32 THEN 'Monthly '
				+ CASE freq_relative_interval
					WHEN 1 THEN 'Every First '
					WHEN 2 THEN 'Every Second '
					WHEN 4 THEN 'Every Third '
					WHEN 8 THEN 'Every Fourth '
					WHEN 16 THEN 'Every Last '
				  END
				+ CASE schedule.freq_interval
					WHEN 1 THEN ' on Monday'
					WHEN 2 THEN ' on Tuesdsay'
					WHEN 3 THEN ' on Wednesday'
					WHEN 4 THEN ' on Thursday'
					WHEN 5 THEN ' on Friday'
					WHEN 6 THEN ' on Saturday'
					WHEN 7 THEN ' on Sunday'
					WHEN 8 THEN 'Day'
					WHEN 9 THEN 'Week day'
					WHEN 10 THEN 'Weekend day'
				  END
		WHEN 64 THEN 'Startup'
		WHEN 128 THEN 'Idle'
		ELSE 'Err'
	 END AS Schedule,
	CASE schedule.freq_subday_type
		WHEN 1 THEN 'Runs at:'
		WHEN 2 THEN 'Every ' + CONVERT(varchar(3), freq_subday_interval) + ' Seconds'
		WHEN 4 THEN 'Every ' + convert(varchar(3), freq_subday_interval) + ' Minutes'
		WHEN 8 THEN 'Every ' + convert(varchar(3), freq_subday_interval) + ' Hours'
	 END as Frequency,
	SUBSTRING(RIGHT(STUFF(' ', 1, 1, '000000') + CONVERT(varchar(6),active_start_time), 6), 1, 2)
		+ ':' +
	SUBSTRING(RIGHT(STUFF(' ', 1, 1, '000000') + CONVERT(varchar(6),active_start_time), 6) ,3 ,2)
		+ ':' +
	SUBSTRING(RIGHT(STUFF(' ', 1, 1, '000000') + CONVERT(varchar(6),active_start_time),6) ,5 ,2) AS StartAt,
	CASE freq_subday_type
		WHEN 1 THEN NULL
		ELSE
		  SUBSTRING(RIGHT(STUFF(' ', 1, 1, '000000') + CONVERT(varchar(6),active_end_time), 6), 1, 2)
			+ ':' +
		  SUBSTRING(RIGHT(STUFF(' ', 1, 1, '000000') + CONVERT(varchar(6), active_end_time),6) ,3 ,2)
			+ ':' +
		  SUBSTRING(RIGHT(STUFF(' ', 1, 1, '000000') + CONVERT(varchar(6), active_end_time),6) ,5 ,2)
	END AS EndAt,
	avg_duration
FROM msdb.dbo.sysdbmaintplans plans
	INNER JOIN #tmp_backup_plans ON
		plans.plan_id = #tmp_backup_plans.plan_id
	INNER JOIN msdb.dbo.sysdbmaintplan_jobs jobs ON
		#tmp_backup_plans.plan_id = jobs.plan_id
	INNER JOIN msdb.dbo.sysjobs job ON
		jobs.job_id = job.job_id
	INNER JOIN msdb.dbo.sysjobschedules schedule ON
		job.job_id = schedule.job_id
WHERE schedule.enabled = 1
UNION
SELECT 	NULL AS plan_id, jobs.name AS plan_name, #tmp_sls_jobs.type, #tmp_sls_jobs.job_id
	,CASE schedule.freq_type
		WHEN 1 THEN 'Run Once'
		WHEN 4 THEN 'Daily'
		WHEN 8 THEN 'Every '
				+ CASE freq_interval & 2 WHEN 2 THEN ' Monday' ELSE '' END
				+ CASE freq_interval & 4 WHEN 4 THEN ' Tuesday' ELSE '' END
				+ CASE freq_interval & 8 WHEN 8 THEN ' Wednesday' ELSE '' END
				+ CASE freq_interval & 16 WHEN 16 THEN ' Thursday' ELSE '' END
				+ CASE freq_interval & 32 WHEN 32 THEN ' Friday' ELSE '' END
				+ CASE freq_interval & 64 WHEN 64 THEN ' Saturday' ELSE '' END
				+ CASE freq_interval & 1 WHEN 1 THEN ' on Sunday' ELSE '' END
		WHEN 16 THEN 'Monthly on day ' + CONVERT(varchar(2), freq_interval)
		WHEN 32 THEN 'Monthly '
				+ CASE freq_relative_interval
					WHEN 1 THEN 'Every First '
					WHEN 2 THEN 'Every Second '
					WHEN 4 THEN 'Every Third '
					WHEN 8 THEN 'Every Fourth '
					WHEN 16 THEN 'Every Last '
				  END
				+ CASE schedule.freq_interval
					WHEN 1 THEN ' on Monday'
					WHEN 2 THEN ' on Tuesdsay'
					WHEN 3 THEN ' on Wednesday'
					WHEN 4 THEN ' on Thursday'
					WHEN 5 THEN ' on Friday'
					WHEN 6 THEN ' on Saturday'
					WHEN 7 THEN ' on Sunday'
					WHEN 8 THEN 'Day'
					WHEN 9 THEN 'Week day'
					WHEN 10 THEN 'Weekend day'
				  END
		WHEN 64 THEN 'Startup'
		WHEN 128 THEN 'Idle'
		ELSE 'Err'
	 END AS Schedule,
	CASE schedule.freq_subday_type
		WHEN 1 THEN 'Runs at:'
		WHEN 2 THEN 'Every ' + CONVERT(varchar(3), freq_subday_interval) + ' Seconds'
		WHEN 4 THEN 'Every ' + convert(varchar(3), freq_subday_interval) + ' Minutes'
		WHEN 8 THEN 'Every ' + convert(varchar(3), freq_subday_interval) + ' Hours'
	 END as Frequency,
	SUBSTRING(RIGHT(STUFF(' ', 1, 1, '000000') + CONVERT(varchar(6),active_start_time), 6), 1, 2)
		+ ':' +
	SUBSTRING(RIGHT(STUFF(' ', 1, 1, '000000') + CONVERT(varchar(6),active_start_time), 6) ,3 ,2)
		+ ':' +
	SUBSTRING(RIGHT(STUFF(' ', 1, 1, '000000') + CONVERT(varchar(6),active_start_time),6) ,5 ,2) AS StartAt,
	CASE freq_subday_type
		WHEN 1 THEN NULL
		ELSE
		  SUBSTRING(RIGHT(STUFF(' ', 1, 1, '000000') + CONVERT(varchar(6),active_end_time), 6), 1, 2)
			+ ':' +
		  SUBSTRING(RIGHT(STUFF(' ', 1, 1, '000000') + CONVERT(varchar(6), active_end_time),6) ,3 ,2)
			+ ':' +
		  SUBSTRING(RIGHT(STUFF(' ', 1, 1, '000000') + CONVERT(varchar(6), active_end_time),6) ,5 ,2)
	END AS EndAt,
	avg_duration
FROM msdb..sysjobs jobs
	INNER JOIN #tmp_sls_jobs ON
		#tmp_sls_jobs.job_id = jobs.job_id
	INNER JOIN msdb.dbo.sysjobschedules schedule ON
		#tmp_sls_jobs.job_id = schedule.job_id
WHERE schedule.enabled = 1


DROP TABLE #tmp_sls_jobs
DROP TABLE #tmp_backup_plans
DROP TABLE #tmp_sls_durations
DROP TABLE #tmp_backup_durations
DROP FUNCTION fn_AgentTime2Bigint