SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object:  SQL_SCALAR_FUNCTION  fun_jobschedule2description       Script Date: 2026-05-15 19:31:09.380 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[fun_jobschedule2description]')) DROP FUNCTION [dbo].[fun_jobschedule2description];
GO


CREATE FUNCTION [dbo].[fun_jobschedule2description](
  @enabled                INT,
  @freq_type              INT,
  @freq_interval          INT,
  @freq_subday_type       INT,
  @freq_subday_interval   INT,
  @freq_relative_interval INT,
  @freq_recurrence_factor INT,
  @active_start_date      INT,
  @active_end_date        INT,
  @active_start_time      INT,
  @active_end_time        INT)
RETURNS VARCHAR(255)
AS
BEGIN
/*
SQLServer User-Defined Function

PURPOSE
Convert a jobschedule to readble text.

Input parameters can be derived from msdb.dbo.sysschedules table.
Text format of the schedule does not exactly follow the Management Studio format.
If active_start_date is passed, no "starting on " is added.
If active_end_date = 99991231, no "ending on " is added.

SELECT
  [name],
  [dbo].[fun_jobschedule2description](
    [enabled],
    [freq_type],
    [freq_interval],
    [freq_subday_type],
    [freq_subday_interval],
    [freq_relative_interval],
    [freq_recurrence_factor],
    [active_start_date],
    [active_end_date],
    [active_start_time],
    [active_end_time]) AS [schedule_desc]
FROM [msdb].[dbo].[sysschedules]

HISTORY
2000-01-01 - Created function
2024-02-09 - Changed history table

TAGS
<program>
  <description>Convert a jobschedule to readble text</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2000-01-01</created>
  <lastmodified>2024-02-09</lastmodified>
</program>
*/
  DECLARE @schedule_desc VARCHAR(255);
  SET @schedule_desc = 
  CASE @freq_type
    WHEN 0x1 THEN 'Once on ' + CONVERT(VARCHAR,CONVERT(DATETIME,CONVERT(VARCHAR,@active_start_date)),105)
    WHEN 0x4 THEN CASE WHEN @freq_interval = 1 THEN 'Daily' WHEN @freq_interval > 1 THEN 'Every ' + CONVERT(VARCHAR,@freq_interval) + ' days' END
    WHEN 0x8 THEN CASE WHEN @freq_recurrence_factor = 1 THEN 'Weekly on ' WHEN @freq_recurrence_factor > 1 THEN 'Every ' + CONVERT(VARCHAR,@freq_recurrence_factor) + ' weeks on ' END + 
      dbo.fun_trim(
          CASE WHEN @freq_interval &  1 =  1 THEN 'Sunday, '    ELSE '' END
        + CASE WHEN @freq_interval &  2 =  2 THEN 'Monday, '    ELSE '' END
        + CASE WHEN @freq_interval &  4 =  4 THEN 'Tuesday, '   ELSE '' END
        + CASE WHEN @freq_interval &  8 =  8 THEN 'Wednesday, ' ELSE '' END
        + CASE WHEN @freq_interval & 16 = 16 THEN 'Thursday, '  ELSE '' END
        + CASE WHEN @freq_interval & 32 = 32 THEN 'Friday, '    ELSE '' END
        + CASE WHEN @freq_interval & 64 = 64 THEN 'Saturday, '  ELSE '' END, ', ')
    WHEN 0x10 THEN CASE WHEN @freq_recurrence_factor = 1 THEN 'Monthly on the ' WHEN @freq_recurrence_factor > 1 THEN 'Every ' + CONVERT(VARCHAR,@freq_recurrence_factor) + ' months on the ' END +
      CONVERT(VARCHAR,@freq_interval) +
      CASE WHEN @freq_interval IN (1, 21, 31) THEN 'st' WHEN @freq_interval IN (2, 22) THEN 'nd' WHEN @freq_interval IN (3, 23) THEN 'rd' ELSE 'th' END
    WHEN 0x20 THEN
      CASE
        WHEN @freq_recurrence_factor = 1 THEN 'Monthly on the '
        WHEN @freq_recurrence_factor > 1 THEN 'Every ' + CONVERT(VARCHAR,@freq_recurrence_factor) + ' months on the ' END +
      CASE @freq_relative_interval
        WHEN 0x01 THEN 'first '
        WHEN 0x02 THEN 'second '
        WHEN 0x04 THEN 'third '
        WHEN 0x08 THEN 'fourth '
        WHEN 0x10 THEN 'last ' END +
      CASE @freq_interval
        WHEN  1 THEN 'Sunday'
        WHEN  2 THEN 'Monday'
        WHEN  3 THEN 'Tuesday'
        WHEN  4 THEN 'Wednesday'
        WHEN  5 THEN 'Thursday'
        WHEN  6 THEN 'Friday'
        WHEN  7 THEN 'Saturday'
        WHEN  8 THEN 'day'
        WHEN  9 THEN 'week day'
        WHEN 10 THEN 'weekend day' END
    WHEN 0x40 THEN 'Starts automatically when SQLServerAgent starts'
    WHEN 0x80 THEN 'Starts whenever the CPUs become idle'
    ELSE '' END +
  CASE
     WHEN (@freq_subday_type = 0x1 OR @freq_type = 0x1) THEN ' at ' + ISNULL(dbo.fun_jobtime2time(@active_start_time),'')
     WHEN @freq_subday_type IN (0x2, 0x4, 0x8) THEN ' every ' + CONVERT(VARCHAR,@freq_subday_interval) + 
       CASE @freq_subday_type 
         WHEN 0x2 THEN ' second'
         WHEN 0x4 THEN ' minute'
         WHEN 0x8 THEN ' hour' END +
       CASE
         WHEN @freq_subday_interval > 1 THEN 's'
         ELSE '' END
      ELSE '' END +
  CASE
    WHEN @freq_subday_type IN ( 0x2, 0x4, 0x8 ) THEN ' between ' + ISNULL(dbo.fun_jobtime2time(@active_start_time),'')
         + ' and ' + ISNULL(dbo.fun_jobtime2time(@active_end_time),'')
    ELSE '' END +
  CASE 
    WHEN (@freq_type IN (0x4, 0x8, 0x10, 0x20) AND CONVERT(DATETIME,CONVERT(VARCHAR,@active_start_date)) > CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),105),105)) THEN ' starting on ' + CONVERT(VARCHAR,CONVERT(DATETIME,CONVERT(VARCHAR,@active_start_date)),105)
    ELSE '' END +
  CASE 
    WHEN (@freq_type IN (0x4, 0x8, 0x10, 0x20) AND @active_end_date <> 99991231) THEN ' ending on ' + CONVERT(VARCHAR,CONVERT(DATETIME,CONVERT(VARCHAR,@active_end_date)),105)
    ELSE '' END +
  CASE 
    WHEN @enabled = 0 THEN ' (Disabled)' 
    ELSE '' END

  RETURN @schedule_desc;
END


GO

/****** Object:  SQL_SCALAR_FUNCTION  fun_jobtime2time       Script Date: 2026-05-15 19:31:09.380 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[fun_jobtime2time]')) DROP FUNCTION [dbo].[fun_jobtime2time];
GO


CREATE FUNCTION [dbo].[fun_jobtime2time] (
  @jobtime INT)
RETURNS VARCHAR(8)
AS
BEGIN
/*
SQLServer User-Defined Function

PURPOSE
Convert job time (int) to fixed format hh:mi:ss.

The meaning of the output format hh:mi:ss is hh=hours (24), mi=minutes, ss=seconds.

The @jobtime must be specified as ss, miss or hhmiss (hh=0-23, mi=0-59, ss=0-59)

EXAMPLES
1)
SELECT dbo.fun_jobtime2time(0)
00:00:00

2)
SELECT dbo.fun_jobtime2time(1)
00:00:01

3)
SELECT dbo.fun_jobtime2time(1000)
00:10:00

4)
SELECT dbo.fun_jobtime2time(235959)
23:59:59

5)
SELECT dbo.fun_jobtime2time(-1)
ERR_NEG

6)
SELECT dbo.fun_jobtime2time(60)
ERR_MAX

HISTORY
2014-11-24 - Created function
2024-02-09 - Changed history table

TAGS
<program>
  <description>Convert job time (int) to fixed format hh:mi:ss</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2014-11-24</created>
  <lastmodified>2024-02-09</lastmodified>
</program>
*/
  DECLARE @out VARCHAR(12)
  DECLARE @h INT
  DECLARE @m INT
  DECLARE @s INT

  SET @out = '';
  SET @h = @jobtime/10000;
  SET @m = @jobtime/100%100;
  SET @s = @jobtime%100;

  IF @jobtime < 0
  BEGIN
    SET @out = 'ERR_NEG';
  END
  ELSE
  BEGIN
    SET @h = @jobtime/10000;
    SET @m = @jobtime/100%100;
    SET @s = @jobtime%100;
 
    IF @h>23 OR @m>59 OR @s>59
    BEGIN
      SET @out = 'ERR_MAX';
    END
    ELSE
    BEGIN
      SET @out = 
        RIGHT(CONVERT(VARCHAR,@h+100),2)+':'+
        RIGHT(CONVERT(VARCHAR,@m+100),2)+':'+
        RIGHT(CONVERT(VARCHAR,@s+100),2);
    END
  END
  RETURN @out;
END


GO

/****** Object:  SQL_SCALAR_FUNCTION  fun_trim       Script Date: 2026-05-15 19:31:09.383 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[fun_trim]')) DROP FUNCTION [dbo].[fun_trim];
GO


CREATE FUNCTION [dbo].[fun_trim] (
  @str VARCHAR(MAX),
  @char VARCHAR(1))
RETURNS VARCHAR(MAX)
AS
BEGIN
/*
SQLServer User-Defined Function

PURPOSE
Trim given text left and right with given character, linefeed-, carriagereturn- or space characters.

EXAMPLES
1)
SELECT dbo.fun_trim('  Category  ', '')
Category

2)
SELECT dbo.fun_trim(' "Category" ', '"')
Category

3)
SELECT dbo.fun_trim(CHAR(13)+CHAR(10)+'    Category', ' ')
Category

4)
SELECT dbo.fun_trim('x'+CHAR(13)+CHAR(10)+'    Category', ' ')
x
    Category

HISTORY
2012-04-19 - Created function
2024-02-09 - Changed history table
2026-03-30 - Changed examples

TAGS
<program>
  <description>Trim given text left and right with given character, linefeed-, carriagereturn- or space characters</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2012-04-19</created>
  <lastmodified>2026-03-30</lastmodified>
</program>
*/
  DECLARE @text VARCHAR(MAX)
  DECLARE @one VARCHAR(1)
  DECLARE @len INT

  SET @text = @str;

  SET @len = DATALENGTH(@text);
  IF @len > 0
  BEGIN
    -- trim right
    SET @one = RIGHT(@text,1);
    WHILE @one IN (@char, CHAR(10), CHAR(13), CHAR(32))
    BEGIN
      SET @text = LEFT(@text,DATALENGTH(@text)-1);
      SET @len = DATALENGTH(@text);
      IF @len = 0 BREAK;
      SET @one = RIGHT(@text,1);
    END
  END

  SET @len = DATALENGTH(@text);
  IF @len > 0
  BEGIN
    -- trim left
    SET @one = LEFT(@text,1);
    WHILE @one IN (@char, CHAR(10), CHAR(13), CHAR(32))
    BEGIN
      SET @text = RIGHT(@text,DATALENGTH(@text)-1);
      SET @len = DATALENGTH(@text);
      IF @len = 0 BREAK;
      SET @one = LEFT(@text,1);
    END
  END

  RETURN @text;
END


GO

/****** Object:  SQL_STORED_PROCEDURE  prc_create_backup_jobs       Script Date: 2026-05-15 19:31:09.383 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[prc_create_backup_jobs]')) DROP PROCEDURE [dbo].[prc_create_backup_jobs];
GO

CREATE PROCEDURE [dbo].[prc_create_backup_jobs] (
  @backup_path VARCHAR(255) = '',
  @subdir BIT = 1,
  @verify BIT = 0,
  @split BIT = 0,
  @keep_days INT = 90,
  @force_full_backup BIT = 1,
  @full_backup_weekday VARCHAR(3) = 'SAT',
  @full_backup_start_hour INT = 0,
  @log_backup_interval_minutes INT = 60,
  @system_backup_interval_hours INT = 4,
  @error_report_path VARCHAR(255) = '',
  @operator_name NVARCHAR(128) = '',
  @email_address NVARCHAR(100) = '',
  @option VARCHAR(6) = 'CREATE') AS
BEGIN
/*
SQLServer Stored Procedure

PURPOSE
Create jobs for backup- and check_backup procedures.

PARAMETERS
 -  1 @backup_path                  VARCHAR(255)   Path to save backupfiles to.         
 -  2 @subdir                       BIT            Add database name as subdirectory to backup path, 0 / 1 (default).
 -  3 @verify                       BIT            Verify backups, 0 (default) / 1.
 -  4 @split                        BIT            Split userdb full backupfiles when large database, 0 (default) / 1.
 -  5 @keep_days                    INT            Delete backupfiles older than keep days, 90 (default) / 0 / n. Value 0 means no cleanup.r
 -  6 @force_full_backup            BIT            Force a full backup during diff or log backup when there is no full backup found in the msdb history, 0 (default) / 1.
 -  7 @full_backup_weekday          VARCHAR(3)     Job schedule weekday for the full backup job, 'SAT','SUN', 'MON', 'TUE', 'WED', 'THU' or 'FRI'.
 -  8 @full_backup_start_hour       INT            Job schedule start hour for the full- and diff backup jobs (0-23).
 -  9 @log_backup_interval_minutes  INT            Job schedule interval in minutes for the user databases transaction log backup, 15 / 30 / 60 (default).
 - 10 @system_backup_interval_hours INT            Job schedule interval in hours for the system databases backup, 1 / 2 / 4 (default) / 6 / 8 / 12 / 24.
 - 11 @error_report_path            VARCHAR(255)   Path to deliver the error report file.
 - 12 @operator_name                NVARCHAR(128)  Operator name to notify failed jobs.
 - 13 @email_address                NVARCHAR(100)  Operator email address. Also used to deliver error report files.
 - 14 @option                       VARCHAR(6)     'CREATE' (default) (re)create backup jobs / 'LIST' List backup jobs

Job Category 'Database Maintenance' is created if not exists.
Job Operator with name @operator_name and email_address @email_address is created if not exists.
Jobs are (re)created:
 - Name: {dbname} - Backup Systemdb          Schedule: Daily every @system_backup_interval_hours hours between 00:55:00 and 23:59:59
 - Name: {dbname} - Backup Userdb Full       Schedule: Weekly on @full_backup_weekday at @full_backup_start_hour hh:05:00
 - Name: {dbname} - Backup Userdb Diff       Schedule: Weekly on every other weekday than @full_backup_weekday at @full_backup_start_hour hh:05:00
 - Name: {dbname} - Backup Userdb Log        Schedule: Daily every @log_backup_interval_minutes minutes between 00:00:00 and 23:59:59
 - Name: {dbname} - Check database backup    Schedule: Daily every 1 hour between 00:50:00 and 23:59:59

EXAMPLES
-- Create backup jobs using all defaults.
EXEC [dbo].[prc_create_backup_jobs]
  @backup_path='C:\Data\MSSQL\backup',
  @error_report_path='C:\Data\MSSQL\error_report',
  @operator_name='SQL Beheerder',
  @email_address='g.mantel@gmail.com';

-- List backup jobs
EXEC [dbo].[prc_create_backup_jobs]
  @option = 'LIST';

USED OBJECTS
 - dbo.fun_jobschedule2description (udf in local database)
    -  dbo.fun_trim                (udf in local database)
    -  dbo.fun_jobtime2time        (udf in local database)

HISTORY
2026-05-12 - Created procedure
2026-04-13 - Implemented @force_full_backup for procedure prc_backup_userdb_diff
           - Changed parameter @force_full_backup default to 1
           - Minimum value for @log_backup_interval_minutes is now 15 minutes.

TAGS
<program>
  <description>Create jobs for backup- and check backup procedures</description>
  <generic>0</generic>
  <author>Gerrit Mantel</author>
  <created>2026-05-12</created>
  <lastmodified>2026-05-13</lastmodified>
</program>
*/
  SET NOCOUNT ON

  DECLARE @message VARCHAR(512)
  DECLARE @dbname NVARCHAR(128)
  DECLARE @start_date INT
  
  DECLARE @jobname_backup_userdb_full NVARCHAR(128)
  DECLARE @jobname_backup_userdb_diff NVARCHAR(128)
  DECLARE @jobname_backup_userdb_log NVARCHAR(128)
  DECLARE @jobname_backup_systemdb NVARCHAR(128)
  DECLARE @jobname_check_database_backup NVARCHAR(128)
  DECLARE @time_backup_userdb_full INT
  DECLARE @interval_backup_userdb_full INT
  DECLARE @interval_backup_userdb_diff INT
  DECLARE @cmd_backup_userdb_full NVARCHAR(MAX)
  DECLARE @cmd_backup_userdb_diff NVARCHAR(MAX)
  DECLARE @cmd_backup_userdb_log NVARCHAR(MAX)
  DECLARE @cmd_backup_systemdb NVARCHAR(MAX)
  DECLARE @cmd_check_database_backup NVARCHAR(MAX)

  DECLARE @jobId BINARY(16)
  DECLARE @scheduleId INT
  DECLARE @schedule_desc VARCHAR(MAX)

  IF (SELECT OBJECT_ID('tempdb..#temp_status_jobs1')) IS NOT NULL
    DROP TABLE #temp_status_jobs1;

  CREATE TABLE #temp_status_jobs1 (
    [job_name] NVARCHAR(128) NOT NULL,
	  [job_id] UNIQUEIDENTIFIER NOT NULL,
    [job_enabled] VARCHAR(3) NULL,
    [owner] NVARCHAR(128) NULL,
    [category] NVARCHAR(128) NULL,
    [notify_email_operator] NVARCHAR(128) NULL,
    [notify_level_email] VARCHAR(50) NULL,
    [date_created] DATETIME NULL,
    [schedule_desc] VARCHAR(MAX) NULL,
    [next_scheduled_run_date] DATETIME NULL,
  CONSTRAINT [pk_temp_status_jobs1] PRIMARY KEY CLUSTERED ([job_name] ASC));

  CREATE UNIQUE NONCLUSTERED INDEX [ix_temp_status_jobs1] ON #temp_status_jobs1 ([job_id] ASC);

  IF (SELECT OBJECT_ID('tempdb..#temp_status_jobs2')) IS NOT NULL
    DROP TABLE #temp_status_jobs2;

  CREATE TABLE #temp_status_jobs2 (
	  [job_id] UNIQUEIDENTIFIER NOT NULL,
	  [schedule_id] INT NOT NULL,
  CONSTRAINT [pk_temp_status_jobs2] PRIMARY KEY CLUSTERED ([job_id] ASC, [schedule_id] ASC));

  IF (SELECT OBJECT_ID('tempdb..#temp_status_jobs3')) IS NOT NULL
    DROP TABLE #temp_status_jobs3;

  CREATE TABLE #temp_status_jobs3 (
	  [schedule_id] INT NOT NULL,
	  [enabled] INT,
    [schedule_desc] VARCHAR(255),
  CONSTRAINT [pk_temp_status_jobs3] PRIMARY KEY CLUSTERED ([schedule_id] ASC));

  -- Set jobnames
  SET @dbname = DB_NAME();
  SET @jobname_backup_userdb_full    = @dbname + ' - Backup Userdb Full';
  SET @jobname_backup_userdb_diff    = @dbname + ' - Backup Userdb Diff'
  SET @jobname_backup_userdb_log     = @dbname + ' - Backup Userdb Log'
  SET @jobname_backup_systemdb       = @dbname + ' - Backup Systemdb'
  SET @jobname_check_database_backup = @dbname + ' - Check Database Backup'

  IF ISNULL(@option,'') NOT IN ('CREATE','LIST')
  BEGIN
    SET @message = 'ERROR - Parameter @option must be CREATE or LIST';
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  IF @option = 'LIST' GOTO list;

  IF ISNULL(@backup_path,'') = ''
  BEGIN
    SET @message = 'ERROR - Parameter @backup_path must be provided';
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  IF ISNULL(@full_backup_weekday,'') NOT IN ('MON','TUE','WED','THU','FRI','SAT','SUN')
  BEGIN
    SET @message = 'ERROR - Parameter @full_backup_weekday must be MON, TUE, WED, THU, FRI, SAT or SUN';
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  IF ISNULL(@full_backup_start_hour,-1) NOT BETWEEN 0 AND 23
  BEGIN
    SET @message = 'ERROR - Parameter @full_backup_start_hour must be between 0 and 23';
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  IF ISNULL(@log_backup_interval_minutes,-1) NOT IN (15, 30, 60)
  BEGIN
    SET @message = 'ERROR - Parameter @log_backup_interval_minutes must be 15, 30 or 60';
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  IF ISNULL(@system_backup_interval_hours,-1) NOT IN (1, 2, 3, 4, 6, 8, 12, 24)
  BEGIN
    SET @message = 'ERROR - Parameter @system_backup_interval_hours must be 1, 2, 3, 4, 6, 8, 12 or 24';
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  IF ISNULL(@operator_name,'') = ''
  BEGIN
    SET @message = 'ERROR - Parameter @operator_name must be provided';
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  IF ISNULL(@email_address,'') = ''
  BEGIN
    SET @message = 'ERROR - Parameter @email_address must be provided';
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  -- Set job commands
  SET @cmd_backup_userdb_full = N'EXEC [dbo].[prc_backup_userdb_full]
  @backup_path='''+@backup_path+''',
  @subdir='+CONVERT(VARCHAR,@subdir)+',
  @verify='+CONVERT(VARCHAR,@verify)+',
  @split='+CONVERT(VARCHAR,@split)+',
  @keep_days='+CONVERT(VARCHAR,@keep_days)+';';

  SET @cmd_backup_userdb_diff = N'EXEC [dbo].[prc_backup_userdb_diff]
  @backup_path='''+@backup_path+''',
  @subdir='+CONVERT(VARCHAR,@subdir)+',
  @verify='+CONVERT(VARCHAR,@verify)+',
  @force_full_backup='+CONVERT(VARCHAR,@force_full_backup)+';';

  SET @cmd_backup_userdb_log = N'EXEC [dbo].[prc_backup_userdb_log]
  @backup_path='''+@backup_path+''',
  @subdir='+CONVERT(VARCHAR,@subdir)+',
  @verify='+CONVERT(VARCHAR,@verify)+',
  @force_full_backup='+CONVERT(VARCHAR,@force_full_backup)+';';

  SET @cmd_backup_systemdb = N'EXEC [dbo].[prc_backup_systemdb]
  @backup_path='''+@backup_path+''',
  @subdir='+CONVERT(VARCHAR,@subdir)+',
  @verify='+CONVERT(VARCHAR,@verify)+';';

  SET @cmd_check_database_backup = N'EXEC [dbo].[prc_check_database_backup]
  @log_backup_interval_minutes='+CONVERT(VARCHAR,@log_backup_interval_minutes)+',
  @system_backup_interval_hours='+CONVERT(VARCHAR,@system_backup_interval_hours)+',
  @error_report_path='''+@error_report_path+''',
  @email_address='''+@email_address+''';';

  -- Set start date
  SET @start_date = CONVERT(INT,CONVERT(VARCHAR,GETDATE(),112));

  -- Set start time for full backup (also used for diff backup)
  SET @time_backup_userdb_full = @full_backup_start_hour*10000+500;

  -- Add Job category 'Database Maintenance' if not exists
  IF NOT EXISTS (SELECT '' FROM msdb.dbo.syscategories WHERE [name]=N'Database Maintenance' AND [category_class]=1)
  BEGIN
    EXEC msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
    SET @message = '-> Created job category "Database Maintenance"';
    RAISERROR (@message, 0, 1) WITH NOWAIT;
  END

  -- Add Operator if not exists
  IF NOT EXISTS (SELECT '' FROM msdb.dbo.sysoperators WHERE [name]=@operator_name)
  BEGIN
    EXEC msdb.dbo.sp_add_operator @name=@operator_name, 
      @enabled=1, 
      @email_address=@email_address, 
      @category_name=N'[Uncategorized]';
    SET @message = '-> Created operator "'+@operator_name+'"';
    RAISERROR (@message, 0, 1) WITH NOWAIT;
  END

  -- Set freq intervals
  SET @interval_backup_userdb_full = CASE @full_backup_weekday
    WHEN 'SUN' THEN 1  -- SUN 1
    WHEN 'MON' THEN 2  -- MON 2
    WHEN 'TUE' THEN 4  -- TUE 4
    WHEN 'WED' THEN 8  -- WED 8
    WHEN 'THU' THEN 16 -- THU 16
    WHEN 'FRI' THEN 32 -- FRI 32
    WHEN 'SAT' THEN 64 -- SAT 64
    END;

  SET @interval_backup_userdb_diff = CASE @full_backup_weekday
    WHEN 'SUN' THEN 126 --         MON 2 + TUE 4 + WED 8 + THU 16 + FRI 32 + SAT 64
    WHEN 'MON' THEN 125 -- SUN 1 +         TUE 4 + WED 8 + THU 16 + FRI 32 + SAT 64
    WHEN 'TUE' THEN 123 -- SUN 1 + MON 2 +         WED 8 + THU 16 + FRI 32 + SAT 64
    WHEN 'WED' THEN 119 -- SUN 1 + MON 2 + TUE 4         + THU 16 + FRI 32 + SAT 64
    WHEN 'THU' THEN 111 -- SUN 1 + MON 2 + TUE 4 + WED 8 +        + FRI 32 + SAT 64
    WHEN 'FRI' THEN 95  -- SUN 1 + MON 2 + TUE 4 + WED 8 + THU 16 +          SAT 64
    WHEN 'SAT' THEN 63  -- SUN 1 + MON 2 + TUE 4 + WED 8 + THU 16 + FRI 32
    END;

  -- Backup Userdb Full
  SET @jobId = (SELECT [job_id] FROM [msdb].[dbo].[sysjobs] WHERE [name] = @jobname_backup_userdb_full);
  IF @jobId IS NOT NULL
  BEGIN
    IF EXISTS (SELECT '' FROM [msdb].[dbo].[sysjobschedules] WHERE [job_id] = @jobId)
    BEGIN
      DECLARE my_cur_schedules CURSOR READ_ONLY FOR SELECT [schedule_id] FROM [msdb].[dbo].[sysjobschedules] WHERE [Job_id] = @jobId;
      OPEN my_cur_schedules
      WHILE 1 < 2
      BEGIN
        FETCH NEXT FROM my_cur_schedules INTO @scheduleId
        IF @@FETCH_STATUS <> 0 BREAK
        -- Detach jobschedule from job
        EXEC msdb.dbo.sp_detach_schedule @job_id=@jobId, @schedule_id=@scheduleId;
        -- Delelete schedule
        EXEC msdb.dbo.sp_delete_schedule @schedule_id=@scheduleId;
      END
      CLOSE my_cur_schedules
      DEALLOCATE my_cur_schedules
    END
    -- Delete job
    EXEC msdb.dbo.sp_delete_job @job_id=@jobId;
    SET @message = '-> Deleted job "'+@jobname_backup_userdb_full+'"';
    RAISERROR (@message, 0, 1) WITH NOWAIT;
  END

  -- Add Job, Add Jobstep and Add Jobschedule
  -- Must set @jobId to NULL before sp_add_job after sp_deletejob, See https://stackoverflow.com/questions/3073177/error-14274-cant-delete-then-re-add-job
  SET @jobId = NULL;
  EXEC msdb.dbo.sp_add_job @job_name=@jobname_backup_userdb_full, 
  @enabled=1, 
  @notify_level_eventlog=0, 
  @notify_level_email=2, -- Email on failure
  @notify_level_netsend=0, 
  @notify_level_page=0, 
  @delete_level=0, 
  @description=N'No description available.', 
  @category_name=N'Database Maintenance', 
  @owner_login_name=N'sa', 
  @notify_email_operator_name=@operator_name, 
  @job_id=@jobId OUTPUT;

  EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=@jobname_backup_userdb_full, 
    @step_id=1, 
    @cmdexec_success_code=0, 
    @on_success_action=1, 
    @on_success_step_id=0, 
    @on_fail_action=2, 
    @on_fail_step_id=0, 
    @retry_attempts=0, 
    @retry_interval=0, 
    @os_run_priority=0, @subsystem=N'TSQL', 
    @command=@cmd_backup_userdb_full, 
    @database_name=@dbname, 
    @flags=8; -- Write log to table (overwrite existing history)

  EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=@jobname_backup_userdb_full, 
    @enabled=1, 
    @freq_type=8,  -- Weekly
    @freq_interval=@interval_backup_userdb_full, 
    @freq_subday_type=1, 
    @freq_subday_interval=0, 
    @freq_relative_interval=0, 
    @freq_recurrence_factor=1, 
    @active_start_date=@start_date, 
    @active_end_date=99991231, 
    @active_start_time=@time_backup_userdb_full, 
    @active_end_time=235959;

  EXEC msdb.dbo.sp_add_jobserver @job_id=@jobId, @server_name=N'(local)';
  SET @message = '-> Created job "'+@jobname_backup_userdb_full+'"';
  RAISERROR (@message, 0, 1) WITH NOWAIT;

  -- Backup Userdb Diff
  SET @jobId = (SELECT [job_id] FROM [msdb].[dbo].[sysjobs] WHERE [name] = @jobname_backup_userdb_diff);
  IF @jobId IS NOT NULL
  BEGIN
    IF EXISTS (SELECT '' FROM [msdb].[dbo].[sysjobschedules] WHERE [job_id] = @jobId)
    BEGIN
      DECLARE my_cur_schedules CURSOR READ_ONLY FOR SELECT [schedule_id] FROM [msdb].[dbo].[sysjobschedules] WHERE [Job_id] = @jobId;
      OPEN my_cur_schedules
      WHILE 1 < 2
      BEGIN
        FETCH NEXT FROM my_cur_schedules INTO @scheduleId
        IF @@FETCH_STATUS <> 0 BREAK
        -- Detach jobschedule from job
        EXEC msdb.dbo.sp_detach_schedule @job_id=@jobId, @schedule_id=@scheduleId;
        -- Delelete schedule
        EXEC msdb.dbo.sp_delete_schedule @schedule_id=@scheduleId;
      END
      CLOSE my_cur_schedules
      DEALLOCATE my_cur_schedules
    END
    -- Delete job
    EXEC msdb.dbo.sp_delete_job @job_id=@jobId;
    SET @message = '-> Deleted job "'+@jobname_backup_userdb_diff+'"';
    RAISERROR (@message, 0, 1) WITH NOWAIT;
  END

  -- Add Job, Add Jobstep and Add Jobschedule
  -- Must set @jobId to NULL before sp_add_job after sp_deletejob, See https://stackoverflow.com/questions/3073177/error-14274-cant-delete-then-re-add-job
  SET @jobId = NULL;
  EXEC msdb.dbo.sp_add_job @job_name=@jobname_backup_userdb_diff, 
  @enabled=1, 
  @notify_level_eventlog=0, 
  @notify_level_email=2, -- Email on failure
  @notify_level_netsend=0, 
  @notify_level_page=0, 
  @delete_level=0, 
  @description=N'No description available.', 
  @category_name=N'Database Maintenance', 
  @owner_login_name=N'sa', 
  @notify_email_operator_name=@operator_name, 
  @job_id=@jobId OUTPUT;

  EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=@jobname_backup_userdb_diff, 
    @step_id=1, 
    @cmdexec_success_code=0, 
    @on_success_action=1, 
    @on_success_step_id=0, 
    @on_fail_action=2, 
    @on_fail_step_id=0, 
    @retry_attempts=0, 
    @retry_interval=0, 
    @os_run_priority=0, @subsystem=N'TSQL', 
    @command=@cmd_backup_userdb_diff, 
    @database_name=@dbname, 
    @flags=8; -- Write log to table (overwrite existing history)

  EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=@jobname_backup_userdb_diff, 
    @enabled=1, 
    @freq_type=8, -- Weekly
    @freq_interval=@interval_backup_userdb_diff, 
    @freq_subday_type=1, 
    @freq_subday_interval=0, 
    @freq_relative_interval=0, 
    @freq_recurrence_factor=1, 
    @active_start_date=@start_date, 
    @active_end_date=99991231, 
    @active_start_time=@time_backup_userdb_full, -- Use same time as Backup Userdb Full
    @active_end_time=235959;

  EXEC msdb.dbo.sp_add_jobserver @job_id=@jobId, @server_name=N'(local)';
  SET @message = '-> Created job "'+@jobname_backup_userdb_diff+'"';
  RAISERROR (@message, 0, 1) WITH NOWAIT;

  -- Backup Userdb Log
  SET @jobId = (SELECT [job_id] FROM [msdb].[dbo].[sysjobs] WHERE [name] = @jobname_backup_userdb_log);
  IF @jobId IS NOT NULL
  BEGIN
    IF EXISTS (SELECT '' FROM [msdb].[dbo].[sysjobschedules] WHERE [job_id] = @jobId)
    BEGIN
      DECLARE my_cur_schedules CURSOR READ_ONLY FOR SELECT [schedule_id] FROM [msdb].[dbo].[sysjobschedules] WHERE [Job_id] = @jobId;
      OPEN my_cur_schedules
      WHILE 1 < 2
      BEGIN
        FETCH NEXT FROM my_cur_schedules INTO @scheduleId
        IF @@FETCH_STATUS <> 0 BREAK
        -- Detach jobschedule from job
        EXEC msdb.dbo.sp_detach_schedule @job_id=@jobId, @schedule_id=@scheduleId;
        -- Delelete schedule
        EXEC msdb.dbo.sp_delete_schedule @schedule_id=@scheduleId;
      END
      CLOSE my_cur_schedules
      DEALLOCATE my_cur_schedules
    END
    -- Delete job
    EXEC msdb.dbo.sp_delete_job @job_id=@jobId;
    SET @message = '-> Deleted job "'+@jobname_backup_userdb_log+'"';
    RAISERROR (@message, 0, 1) WITH NOWAIT;
  END

  -- Add Job, Add Jobstep and Add Jobschedule
  -- Must set @jobId to NULL before sp_add_job after sp_deletejob, See https://stackoverflow.com/questions/3073177/error-14274-cant-delete-then-re-add-job
  SET @jobId = NULL;
  EXEC msdb.dbo.sp_add_job @job_name=@jobname_backup_userdb_log, 
  @enabled=1, 
  @notify_level_eventlog=0, 
  @notify_level_email=2, -- Email on failure
  @notify_level_netsend=0, 
  @notify_level_page=0, 
  @delete_level=0, 
  @description=N'No description available.', 
  @category_name=N'Database Maintenance', 
  @owner_login_name=N'sa', 
  @notify_email_operator_name=@operator_name, 
  @job_id=@jobId OUTPUT;

  EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=@jobname_backup_userdb_log, 
    @step_id=1, 
    @cmdexec_success_code=0, 
    @on_success_action=1, 
    @on_success_step_id=0, 
    @on_fail_action=2, 
    @on_fail_step_id=0, 
    @retry_attempts=0, 
    @retry_interval=0, 
    @os_run_priority=0, @subsystem=N'TSQL', 
    @command=@cmd_backup_userdb_log, 
    @database_name=@dbname, 
    @flags=8; -- Write log to table (overwrite existing history)

  EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=@jobname_backup_userdb_log, 
    @enabled=1, 
    @freq_type=4, -- Daily
    @freq_interval=1, 
    @freq_subday_type=4, -- Minutes 
    @freq_subday_interval=@log_backup_interval_minutes, 
    @freq_relative_interval=0, 
    @freq_recurrence_factor=1, 
    @active_start_date=@start_date, 
    @active_end_date=99991231, 
    @active_start_time=0, -- Start log backup always at whole hour 
    @active_end_time=235959;

  EXEC msdb.dbo.sp_add_jobserver @job_id=@jobId, @server_name=N'(local)';
  SET @message = '-> Created job "'+@jobname_backup_userdb_log+'"';
  RAISERROR (@message, 0, 1) WITH NOWAIT;

  -- Backup Systemdb
  SET @jobId = (SELECT [job_id] FROM [msdb].[dbo].[sysjobs] WHERE [name] = @jobname_backup_systemdb);
  IF @jobId IS NOT NULL
  BEGIN
    IF EXISTS (SELECT '' FROM [msdb].[dbo].[sysjobschedules] WHERE [job_id] = @jobId)
    BEGIN
      DECLARE my_cur_schedules CURSOR READ_ONLY FOR SELECT [schedule_id] FROM [msdb].[dbo].[sysjobschedules] WHERE [Job_id] = @jobId;
      OPEN my_cur_schedules
      WHILE 1 < 2
      BEGIN
        FETCH NEXT FROM my_cur_schedules INTO @scheduleId
        IF @@FETCH_STATUS <> 0 BREAK
        -- Detach jobschedule from job
        EXEC msdb.dbo.sp_detach_schedule @job_id=@jobId, @schedule_id=@scheduleId;
        -- Delelete schedule
        EXEC msdb.dbo.sp_delete_schedule @schedule_id=@scheduleId;
      END
      CLOSE my_cur_schedules
      DEALLOCATE my_cur_schedules
    END
    -- Delete job
    EXEC msdb.dbo.sp_delete_job @job_id=@jobId;
    SET @message = '-> Deleted job "'+@jobname_backup_systemdb+'"';
    RAISERROR (@message, 0, 1) WITH NOWAIT;
  END

  -- Add Job, Add Jobstep and Add Jobschedule
  -- Must set @jobId to NULL before sp_add_job after sp_deletejob, See https://stackoverflow.com/questions/3073177/error-14274-cant-delete-then-re-add-job
  SET @jobId = NULL;
  EXEC msdb.dbo.sp_add_job @job_name=@jobname_backup_systemdb, 
  @enabled=1, 
  @notify_level_eventlog=0, 
  @notify_level_email=2, -- Email on failure
  @notify_level_netsend=0, 
  @notify_level_page=0, 
  @delete_level=0, 
  @description=N'No description available.', 
  @category_name=N'Database Maintenance', 
  @owner_login_name=N'sa', 
  @notify_email_operator_name=@operator_name, 
  @job_id=@jobId OUTPUT;

  EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=@jobname_backup_systemdb, 
    @step_id=1, 
    @cmdexec_success_code=0, 
    @on_success_action=1, 
    @on_success_step_id=0, 
    @on_fail_action=2, 
    @on_fail_step_id=0, 
    @retry_attempts=0, 
    @retry_interval=0, 
    @os_run_priority=0, @subsystem=N'TSQL', 
    @command=@cmd_backup_systemdb, 
    @database_name=@dbname, 
    @flags=8; -- Write log to table (overwrite existing history)

  EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=@jobname_backup_systemdb, 
    @enabled=1, 
    @freq_type=4, -- Daily
    @freq_interval=1, 
    @freq_subday_type=8, -- Hours
    @freq_subday_interval=@system_backup_interval_hours, 
    @freq_relative_interval=0, 
    @freq_recurrence_factor=1, 
    @active_start_date=@start_date, 
    @active_end_date=99991231, 
    @active_start_time=5500, -- Start systemdb backup always at 5 minutes before whole hour
    @active_end_time=235959;

  EXEC msdb.dbo.sp_add_jobserver @job_id=@jobId, @server_name=N'(local)';
  SET @message = '-> Created job "'+@jobname_backup_systemdb+'"';
  RAISERROR (@message, 0, 1) WITH NOWAIT;

  -- Check Database Backup
  SET @jobId = (SELECT [job_id] FROM [msdb].[dbo].[sysjobs] WHERE [name] = @jobname_check_database_backup);
  IF @jobId IS NOT NULL
  BEGIN
    IF EXISTS (SELECT '' FROM [msdb].[dbo].[sysjobschedules] WHERE [job_id] = @jobId)
    BEGIN
      DECLARE my_cur_schedules CURSOR READ_ONLY FOR SELECT [schedule_id] FROM [msdb].[dbo].[sysjobschedules] WHERE [Job_id] = @jobId;
      OPEN my_cur_schedules
      WHILE 1 < 2
      BEGIN
        FETCH NEXT FROM my_cur_schedules INTO @scheduleId
        IF @@FETCH_STATUS <> 0 BREAK
        -- Detach jobschedule from job
        EXEC msdb.dbo.sp_detach_schedule @job_id=@jobId, @schedule_id=@scheduleId;
        -- Delelete schedule
        EXEC msdb.dbo.sp_delete_schedule @schedule_id=@scheduleId;
      END
      CLOSE my_cur_schedules
      DEALLOCATE my_cur_schedules
    END
    -- Delete job
    EXEC msdb.dbo.sp_delete_job @job_id=@jobId;
    SET @message = '-> Deleted job "'+@jobname_check_database_backup+'"';
    RAISERROR (@message, 0, 1) WITH NOWAIT;
  END

  -- Add Job, Add Jobstep and Add Jobschedule
  -- Must set @jobId to NULL before sp_add_job after sp_deletejob, See https://stackoverflow.com/questions/3073177/error-14274-cant-delete-then-re-add-job
  SET @jobId = NULL;
  EXEC msdb.dbo.sp_add_job @job_name=@jobname_check_database_backup, 
  @enabled=1, 
  @notify_level_eventlog=0, 
  @notify_level_email=2, -- Email on failure
  @notify_level_netsend=0, 
  @notify_level_page=0, 
  @delete_level=0, 
  @description=N'No description available.', 
  @category_name=N'Database Maintenance', 
  @owner_login_name=N'sa', 
  @notify_email_operator_name=@operator_name, 
  @job_id=@jobId OUTPUT;

  EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=@jobname_check_database_backup, 
    @step_id=1, 
    @cmdexec_success_code=0, 
    @on_success_action=1, 
    @on_success_step_id=0, 
    @on_fail_action=2, 
    @on_fail_step_id=0, 
    @retry_attempts=0, 
    @retry_interval=0, 
    @os_run_priority=0, @subsystem=N'TSQL', 
    @command=@cmd_check_database_backup, 
    @database_name=@dbname, 
    @flags=8; -- Write log to table (overwrite existing history)

  EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=@jobname_check_database_backup, 
    @enabled=1, 
    @freq_type=4, -- Daily
    @freq_interval=1, 
    @freq_subday_type=8, -- Hours
    @freq_subday_interval=1, 
    @freq_relative_interval=0, 
    @freq_recurrence_factor=1, 
    @active_start_date=@start_date, 
    @active_end_date=99991231, 
    @active_start_time=5000, -- Start check database backup at 10 minutes before whole hour
    @active_end_time=235959;

  EXEC msdb.dbo.sp_add_jobserver @job_id=@jobId, @server_name=N'(local)';
  SET @message = '-> Created job "'+@jobname_check_database_backup+'"';
  RAISERROR (@message, 0, 1) WITH NOWAIT;

  list:
  -- List jobs
  INSERT INTO #temp_status_jobs1 (
    [job_name],
    [job_id],
    [job_enabled],
    [owner],
    [category],
    [notify_email_operator],
    [notify_level_email],
    [date_created],
    [next_scheduled_run_date])
  SELECT
    t1.[name] AS [job_name],
    t1.[job_id],
    CASE t1.[enabled] WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END AS [job_enabled],
    [msdb].[dbo].SQLAGENT_SUSER_SNAME(t1.[owner_sid]) AS [owner],
    t2.[name] AS [category],
    t3.[name] AS [notify_email_operator],
    CASE [notify_level_email] WHEN 0 THEN 'Never' WHEN 1 THEN 'When the job succeeds' WHEN 2 THEN 'When the job fails' WHEN 3 THEN 'Whenever the job completes' ELSE NULL END AS [notify_level_email],
    t1.[date_created],
    t4.[next_scheduled_run_date]
  FROM [msdb].[dbo].[sysjobs] t1
  OUTER APPLY (
    SELECT [name]
    FROM [msdb].[dbo].[syscategories]
    WHERE [category_id] = t1.[category_id]) t2
  OUTER APPLY (
    SELECT [name]
    FROM [msdb].[dbo].[sysoperators]
    WHERE [id] = t1.[notify_email_operator_id]) t3
  OUTER APPLY (
    SELECT t41.[next_scheduled_run_date]
    FROM [msdb].[dbo].[sysjobactivity] t41
    WHERE t41.[job_id] = t1.[job_id]
    AND t41.[session_id] = (
      SELECT MAX([session_id])
      FROM [msdb].[dbo].[sysjobactivity] t42
      WHERE t42.[job_id] = t41.[job_id])) t4
  WHERE t1.[name] IN (@jobname_backup_userdb_full, @jobname_backup_userdb_diff, @jobname_backup_userdb_log, @jobname_backup_systemdb, @jobname_check_database_backup);

  -- Get jobschedules
  INSERT INTO #temp_status_jobs2 ([schedule_id], [job_id])
  SELECT [schedule_id], [job_id]
  FROM [msdb].[dbo].[sysjobschedules];

  -- Get schedules
  INSERT INTO #temp_status_jobs3 ([schedule_id], [enabled], [schedule_desc])
  SELECT [schedule_id], [enabled],
    dbo.fun_jobschedule2description([enabled], [freq_type], [freq_interval], [freq_subday_type], [freq_subday_interval], [freq_relative_interval], [freq_recurrence_factor], [active_start_date], [active_end_date], [active_start_time], [active_end_time])
  FROM [msdb].[dbo].[sysschedules];

  -- Update schedule_desc per job
  DECLARE my_cur_jobs CURSOR LOCAL FOR
    SELECT [job_id]
    FROM #temp_status_jobs1
    FOR UPDATE OF schedule_desc;

  OPEN my_cur_jobs
  WHILE 1 < 2
  BEGIN
    FETCH NEXT FROM my_cur_jobs INTO @jobId
    IF @@FETCH_STATUS <> 0 BREAK

    SET @schedule_desc = (
      SELECT STRING_AGG(t3.[schedule_desc],', ')
      FROM #temp_status_jobs2 t2
      INNER JOIN #temp_status_jobs3 t3 ON t3.[schedule_id] = t2.[schedule_id]
      WHERE t2.[job_id] = @jobId);

    UPDATE t1
    SET t1.[schedule_desc] = @schedule_desc
    FROM #temp_status_jobs1 t1
    WHERE CURRENT OF my_cur_jobs;
  END
  CLOSE my_cur_jobs
  DEALLOCATE my_cur_jobs

  SELECT
    [job_name],
    [job_enabled],
    [owner],
    [category],
    [notify_email_operator],
    [notify_level_email],
    [date_created],
    [schedule_desc],
    [next_scheduled_run_date]
  FROM #temp_status_jobs1
  ORDER BY [job_name];

  DROP TABLE #temp_status_jobs1;
  DROP TABLE #temp_status_jobs2;
  DROP TABLE #temp_status_jobs3;
END
GO