--\
---) Author: Hans Michiels
---) Creates jobschedules in SQL Server Agent jobs that
---) were created by Ola Hallengren's maintenance solution.
--/
/*
(c) Copyright 2016 - hansmichiels.com
 
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
 
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
 
You should have received a copy of the GNU General Public License
along with this program. If not, see http://www.gnu.org/licenses/.
*/

USE [msdb]
GO

--\-------------------------------------------------------------------------------------
---) DECLARATIONS
--/-------------------------------------------------------------------------------------
DECLARE @cur_days VARCHAR(50)
     -- @cur_days contains a space separated list of weekdays, e.g. 'mon tue wed thu fri sat sun',
     -- or use 'all' for all days.
     -- Please note that using 'mon tue wed thu fri sat sun' will create a weekly job with
     -- all days checked while using 'all' will create a daily job.

DECLARE @cur_start_time INT
     -- @cur_start_time: the starttime of the job in integer format (hhmmss)

DECLARE @cur_subday_interval INT
     -- @cur_subday_interval: N in 'every N seconds/minutes/hours (for seconds max N = 100)
     -- Use 0 when N/A

DECLARE @cur_intraday_unit VARCHAR(7)
     -- @cur_intraday_unit: seconds (s), minutes (m) or hours (h).
     -- For configuration you can use both the full name or the first letter.

DECLARE @cur_delete_any_schedule CHAR(1)
     -- @cur_delete_any_schedule: indicates if any schedule for the given jobname should be deleted.
     -- Use NULL if you want to use the default value (recommended).
     -- It is recommended only to use 'N' if you use multiple schedules for the same job.

DECLARE @default_delete_any_schedule CHAR(1)
     -- @default_delete_any_schedule: valued used if in the configuration below
     -- [delete_any_schedule] is NULL for a specific job.

DECLARE @delete_schedules_only CHAR(1)
     -- @delete_schedules_only: if set to 'Y', only the existing schedules are deleted.
     -- No new schedules are made.
     -- If you use this setting, make sure to set @default_delete_any_schedule also to 'Y'
     -- without overruling it to 'N' for specific jobs.

DECLARE @cur_schedule_id int
DECLARE @cur_job_name SYSNAME
DECLARE @cur_schedule_name SYSNAME
DECLARE @cur_freq_type INT
DECLARE @cur_freq_interval INT
DECLARE @cur_freq_subday_type INT

DECLARE @active_start_date INT
DECLARE @message VARCHAR(500)

SET @active_start_date = CONVERT(INT, CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112));

--\-------------------------------------------------------------------------------------
---) CONFIGURATION
--/-------------------------------------------------------------------------------------
SET @default_delete_any_schedule = 'Y';
-- If you DO NOT want to delete all existing schedules for all jobs in your configuration,
-- change @default_delete_any_schedule to 'N'.
-- Keeping this value on 'Y' is usually safe to do and this prevents that old schedules
-- might in some cases not be deleted which can give trouble due to multiple (conflicting)
-- schedules.
-- !! ONLY IF you have more than one schedule for a job, you should set [delete_any_schedule]
-- in the configuration above to 'N' for that specific job.

SET @delete_schedules_only = 'N';

DECLARE daily_weekly_cursor CURSOR LOCAL STATIC FOR
SELECT CONVERT(SYSNAME, NULL) AS [job_name], CONVERT(SYSNAME, NULL) AS [schedule_name],
        CONVERT(INT, NULL) AS [start_time], CONVERT(VARCHAR(50), NULL) AS [days],
        CONVERT(INT, NULL) AS [subday_interval],
        CONVERT(VARCHAR(7), NULL) AS [intraday_unit],
        CONVERT(CHAR(1), NULL) AS [delete_any_schedule]

-------------[job_name]------------------------------------[schedule]--[start]-[days]-----------------------[subday ]-[intraday]-[delete_any]
-----------------------------------------------------------[name ]--[time ]------------------------------[interval]-[unit ]-[schedule ]
UNION SELECT 'CommandLog Cleanup' , 'Schedule', 180000, 'wed sun' , 0 , NULL , NULL
UNION SELECT 'Output File Cleanup' , 'Schedule', 180500, 'wed sun' , 0 , NULL , NULL
UNION SELECT 'sp_delete_backuphistory' , 'Schedule', 181000, 'wed sun' , 0 , NULL , NULL
UNION SELECT 'sp_purge_jobhistory' , 'Schedule', 181500, 'wed sun' , 0 , NULL , NULL

UNION SELECT 'DatabaseIntegrityCheck - SYSTEM_DATABASES' , 'Schedule', 182000, 'wed sun' , 0 , NULL , NULL
UNION SELECT 'DatabaseIntegrityCheck - USER_DATABASES' , 'Schedule', 191500, 'wed sun' , 0 , NULL , NULL
UNION SELECT 'DatabaseBackup - SYSTEM_DATABASES - FULL' , 'Schedule', 001500, 'all' , 0 , NULL , NULL
UNION SELECT 'DatabaseBackup - USER_DATABASES - FULL' , 'Schedule', 003000, 'all' , 0 , NULL , NULL

UNION SELECT 'IndexOptimize - USER_DATABASES' , 'Schedule', 081000, 'mon tue wed thu fri sat sun', 0 , NULL , NULL

/* -- Weekly full, other days diff example:
UNION SELECT 'DatabaseBackup - USER_DATABASES - FULL' , 'Schedule', 201500, 'fri' , 0 , NULL , NULL
UNION SELECT 'DatabaseBackup - USER_DATABASES - DIFF' , 'Schedule', 201500, 'mon tue wed thu sat sun' , 0 , NULL , NULL
*/
/* -- Transaction log example / Example of use of [delete_any_schedule] with more than one schedule
UNION SELECT 'DatabaseBackup - USER_DATABASES - LOG' , 'Weekdays', 000001, 'mon tue wed thu fri' , 10 , 'minutes', 'Y'
UNION SELECT 'DatabaseBackup - USER_DATABASES - LOG' , 'Weekend' , 000001, 'sat sun' , 2 , 'hours', 'N'
*/
  

--\-------------------------------------------------------------------------------------
---) CREATION OF THE SCHEDULES
--/-------------------------------------------------------------------------------------
OPEN daily_weekly_cursor
FETCH NEXT FROM daily_weekly_cursor
INTO @cur_job_name, @cur_schedule_name, @cur_start_time, @cur_days,
     @cur_subday_interval, @cur_intraday_unit, @cur_delete_any_schedule

WHILE @@FETCH_STATUS = 0
BEGIN

    --\
    ---) Check if it is the dummy row to specify datatypes:
    --/
    IF @cur_job_name IS NULL
    BEGIN
      -- RAISERROR('NULL', 0, 1) WITH NOWAIT;
      FETCH NEXT FROM daily_weekly_cursor
      INTO @cur_job_name, @cur_schedule_name, @cur_start_time, @cur_days,
           @cur_subday_interval, @cur_intraday_unit, @cur_delete_any_schedule
    END

    SET @message = 'Job: ' + @cur_job_name
    RAISERROR(@message , 0, 1) WITH NOWAIT;

    --\
    ---) Get the current schedule_id, if any.
    --/
    SELECT @cur_schedule_id = -1;
    WHILE @cur_schedule_id IS NOT NULL
    BEGIN
        SELECT @cur_schedule_id = (
            SELECT TOP 1 sch.schedule_id
            FROM msdb.dbo.sysschedules sch
              JOIN msdb.dbo.sysjobschedules js
                ON js.schedule_id = sch.schedule_id
              JOIN msdb.dbo.sysjobs jobs
                ON jobs.job_id = js.job_id
             WHERE jobs.name = @cur_job_name
               AND ( ISNULL(@cur_delete_any_schedule, @default_delete_any_schedule) = 'Y' OR sch.name = @cur_schedule_name )
            );

        --\
        ---) If schedule exists, delete it first.
        --/
        IF @cur_schedule_id IS NOT NULL
        BEGIN
            EXEC msdb.dbo.sp_delete_schedule @schedule_id = @cur_schedule_id, @force_delete = 1;
            SET @message = ' ' + 'Schedule deleted with @schedule_id = ' + CONVERT(VARCHAR, @cur_schedule_id)
            RAISERROR(@message , 0, 1) WITH NOWAIT;
        END
    END

    --\
    ---) Set @cur_freq_type depending on days specification.
    --/
    SELECT @cur_freq_type = CASE @cur_days WHEN 'all' THEN 4 ELSE 8 END;

    --\
    ---) Set @cur_freq_interval depending on days specification.
    --/
    SELECT @cur_freq_interval = 0, @cur_days = ' ' + @cur_days + ' ';
    IF CHARINDEX(' all ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 1;
    IF CHARINDEX(' sun ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 1;
    IF CHARINDEX(' mon ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 2;
    IF CHARINDEX(' tue ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 4;
    IF CHARINDEX(' wed ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 8;
    IF CHARINDEX(' thu ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 16;
    IF CHARINDEX(' fri ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 32;
    IF CHARINDEX(' sat ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 64;
        
    IF @cur_freq_interval = 0
    BEGIN
      RAISERROR('Days string does not contain any valid day abbreviations', 16, 1);
    END

    IF @delete_schedules_only != 'Y'
    BEGIN
        IF @cur_subday_interval = 0
        BEGIN
            EXEC msdb.dbo.sp_add_jobschedule @job_name=@cur_job_name, @name=@cur_schedule_name,
          @enabled=1,
          @freq_type=@cur_freq_type,
          @freq_interval=@cur_freq_interval,
          @freq_subday_type=1,
          @freq_subday_interval=0,
          @freq_relative_interval=0,
          @freq_recurrence_factor=1,
          @active_start_date=@active_start_date,
          @active_end_date=99991231,
          @active_start_time=@cur_start_time,
          @active_end_time=235959, @schedule_id = @cur_schedule_id OUTPUT;

            SET @message = ' ' + 'Schedule created for days' + @cur_days + 'at ' + CONVERT(VARCHAR, @cur_start_time)
            RAISERROR(@message , 0, 1) WITH NOWAIT;

        END ELSE BEGIN

            SELECT @cur_freq_subday_type =
            CASE LOWER(LEFT(@cur_intraday_unit, 1))
            WHEN 'h' THEN 8
            WHEN 'm' THEN 4
            WHEN 's' THEN 2
            ELSE 0
            END

            IF @cur_freq_subday_type = 0
            BEGIN
              RAISERROR('''intraday_unit'' must contain a valid value when ''subday_interval'' contains a number greater than 0. Please check your configuration.', 16, 1);
            END

            EXEC msdb.dbo.sp_add_jobschedule @job_name=@cur_job_name, @name=@cur_schedule_name,
          @enabled=1,
          @freq_type=@cur_freq_type,
          @freq_interval=@cur_freq_interval,
          @freq_subday_type=@cur_freq_subday_type,
          @freq_subday_interval=@cur_subday_interval,
          @freq_relative_interval=0,
          @freq_recurrence_factor=1,
          @active_start_date=@active_start_date,
          @active_end_date=99991231,
          @active_start_time=@cur_start_time,
          @active_end_time=235959, @schedule_id = @cur_schedule_id OUTPUT;

            SET @message = ' ' + 'Schedule created for days' + @cur_days + 'every ' + CONVERT(VARCHAR, @cur_subday_interval) + ' ' + @cur_intraday_unit
            RAISERROR(@message , 0, 1) WITH NOWAIT;

        END
    END
    RAISERROR('-----' , 0, 1) WITH NOWAIT;
    
    SET @cur_schedule_id = NULL;
    FETCH NEXT FROM daily_weekly_cursor
    INTO @cur_job_name, @cur_schedule_name, @cur_start_time, @cur_days,
         @cur_subday_interval, @cur_intraday_unit, @cur_delete_any_schedule
END

CLOSE daily_weekly_cursor
DEALLOCATE daily_weekly_cursor