Wenn man ein SSDT Datenbank-Projekt aufbauen möchte ist man in der Regel bestrebt alle Artefakte die es in irgendeiner Art und Weise im Datenbankserver gibt auch in das SSDT Datenbank-Projekt aufzunehmen. Ziel sollte es sein den kompletten Datenbankserver aus der Versionsverwaltung wieder herstellen zu können. In diesem Zuge sollte man auch Datenbankobjekte wie beispielsweise SQL Server Agent Jobs scripten. Während das bei den SQL Server Agent Jobs noch relativ einfach aus dem SQL Server Management Studio heraus machbar ist, ist das für Schedules, also für Zeitpläne, nicht so einfach. Hier gibt es (zumindest nach meinem Kenntnisstand) keine Möglichkeit die Schedules automatisch rauszuscripten. Naja – selbst ist der Mann, deswegen habe ich mal das folgende Script entwickelt:

SELECT
     'DECLARE @schedule_name AS sysname= ''' + s.name + ''', @enabled AS tinyint = ' + CONVERT(varchar(3), s.enabled)
     + ', @freq_type AS int = ' + CONVERT(varchar(12), s.freq_type) + ', @freq_interval AS int = '
     + CONVERT(varchar(12), s.freq_interval) + ', @freq_subday_type AS int = '
     + CONVERT(varchar(12), s.freq_subday_type) + ', @freq_subday_interval AS int = '
     + CONVERT(varchar(12), s.freq_subday_interval) + ', @freq_relative_interval AS int = '
     + CONVERT(varchar(12), s.freq_relative_interval) + ', @freq_recurrence_factor AS int = '
     + CONVERT(varchar(12), s.freq_recurrence_factor) + ', @active_start_date AS int = '
     + CONVERT(varchar(12), s.active_start_date) + ', @active_end_date AS int = '
     + CONVERT(varchar(12), s.active_end_date) + ', @active_start_time AS int = '
     + CONVERT(varchar(12), s.active_start_time) + ', @active_end_time AS int = '
     + CONVERT(varchar(12), active_end_time) + ', @owner_login_name AS sysname= ''' + sl.loginname
     + ''', @Id as int = 0;' + 'SELECT @Id = schedule_id FROM  dbo.sysschedules WHERE name = ''' + s.name + ''';'
     + 'IF @Id > 0
    EXEC dbo.sp_update_schedule
        @schedule_id = @Id
      , @new_name = @schedule_name
      , @enabled = @enabled
      , @freq_type = @freq_type
      , @freq_interval = @freq_interval
      , @freq_subday_type = @freq_subday_type
      , @freq_subday_interval = @freq_subday_interval
      , @freq_relative_interval = @freq_relative_interval
      , @freq_recurrence_factor = @freq_recurrence_factor
      , @active_start_date = @active_start_date
      , @active_end_date = @active_end_date
      , @active_start_time = @active_start_time
      , @active_end_time = @active_end_time
      , @owner_login_name = @owner_login_name;
ELSE
    EXEC sp_add_schedule
        @schedule_name = @schedule_name
      , @enabled = @enabled
      , @freq_type = @freq_type
      , @freq_interval = @freq_interval
      , @freq_subday_type = @freq_subday_type
      , @freq_subday_interval = @freq_subday_interval
      , @freq_relative_interval = @freq_relative_interval
      , @freq_recurrence_factor = @freq_recurrence_factor
      , @active_start_date = @active_start_date
      , @active_end_date = @active_end_date
      , @active_start_time = @active_start_time
      , @active_end_time = @active_end_time
      , @owner_login_name = @owner_login_name;' AS sqlcmd
FROM sysschedules AS s
JOIN sys.syslogins AS sl
ON s.owner_sid = sl.sid;

Dieses Script macht Folgendes: Es liest alle Schedules aus der Tabelle sysschedules aus und erzeugt für jeden Schedule einen Befehl der diese anlegt bzw. ändert. Im Script wird abgefragt ob es die jeweilige Schedule auf dem Zielserver bereits gibt. Ist das nicht der Fall, so wird die Schedule angelegt, gibt es die Schedule schon, dann wird sie aktualisiert. Das bietet uns die Möglichkeit, dass das SSDT Projekt immer deploybar ist und dass man im SSDT-Projekt einfach nur oben die Parameter ändert und der Schedule bei der nächsten Bereitstellung automatisch auf die neuen Parameter umgestellt wird.

Ein Beispiel für das von dem Script oben automatisch generierte Script ist das folgende:

DECLARE
    @schedule_name AS sysname = 'RunAsSQLAgentServiceStartSchedule'
  , @enabled AS tinyint = 1
  , @freq_type AS int = 64
  , @freq_interval AS int = 0
  , @freq_subday_type AS int = 0
  , @freq_subday_interval AS int = 0
  , @freq_relative_interval AS int = 0
  , @freq_recurrence_factor AS int = 0
  , @active_start_date AS int = 20170822
  , @active_end_date AS int = 99991231
  , @active_start_time AS int = 0
  , @active_end_time AS int = 235959
  , @owner_login_name AS sysname = 'sa'
  , @Id AS int = 0;

SELECT
      @Id = schedule_id
FROM  dbo.sysschedules
WHERE name = 'RunAsSQLAgentServiceStartSchedule';

IF @Id > 0
    EXEC dbo.sp_update_schedule
        @schedule_id = @Id
      , @new_name = @schedule_name
      , @enabled = @enabled
      , @freq_type = @freq_type
      , @freq_interval = @freq_interval
      , @freq_subday_type = @freq_subday_type
      , @freq_subday_interval = @freq_subday_interval
      , @freq_relative_interval = @freq_relative_interval
      , @freq_recurrence_factor = @freq_recurrence_factor
      , @active_start_date = @active_start_date
      , @active_end_date = @active_end_date
      , @active_start_time = @active_start_time
      , @active_end_time = @active_end_time
      , @owner_login_name = @owner_login_name;
ELSE
    EXEC sp_add_schedule
        @schedule_name = @schedule_name
      , @enabled = @enabled
      , @freq_type = @freq_type
      , @freq_interval = @freq_interval
      , @freq_subday_type = @freq_subday_type
      , @freq_subday_interval = @freq_subday_interval
      , @freq_relative_interval = @freq_relative_interval
      , @freq_recurrence_factor = @freq_recurrence_factor
      , @active_start_date = @active_start_date
      , @active_end_date = @active_end_date
      , @active_start_time = @active_start_time
      , @active_end_time = @active_end_time
      , @owner_login_name = @owner_login_name;

Dieses Script kann ich jetzt einfach in ein Post-Deployment Script meines SSDT-Projektes einbinden.