I'm moving a SQL Server off old hardware at the moment and one thing that makes life easier if to have the same schedules on the new server, all ready to pick from the UI when you are creating new jobs.

Having to create a new schedule in the middle of this process is a pain and a distraction, check the short video for the comparison between the complex interface to create a schedule and the ease of picking one that already exists.

So, the trick is to get the schedules from your existing server to your new one before you start creating the jobs on your new server.

Details of SQL Server Jobs and Schedules are held in the MSDB system database. It's safe to work with data in here so long as you know what you are doing, attempting to do and have a restorable backup ready to get you out of trouble. Do this process on a test server until you are confident that nothing will go wrong. I wont be able to rescue you if it does, you will be on your own looking for the quickest available SQL contractor in your area.

Now, if we review the two tables in question we see that the sysjobs table has a schedule_id column and the sysschedules table has a schedule_id column. This means there must be a middle table to manage the fact that one job can run on many schedules but also one schedule could be used by many jobs. The many-to-many relationship is handled by the sysjobschedules table.

When you create a SQL Job you are effectively inserting a row in the sysjobs table. Likewise when you create a schedule you insert a row in the sysschedules table and then when you then pick a schedule for a job you insert a row in the sysjobschedules table.

All we need to do is shortcut using the UI to create each and every schedule one by one.

INSERT  INTO [newserver].[msdb].[dbo].[sysschedules]
        ( [schedule_uid] ,
          [originating_server_id] ,
          [name] ,
          [owner_sid] ,
          [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] ,
          [date_created] ,
          [date_modified] ,
          [version_number]
        )
        SELECT  [schedule_uid] ,
                [originating_server_id] ,
                [name] ,
                [owner_sid] ,
                [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] ,
                [date_created] ,
                [date_modified] ,
                [version_number]
        FROM    [oldserver].msdb.[dbo].[sysschedules] AS s
        JOIN    [oldserver].msdb.[dbo].[sysjobschedules] AS s2
        ON      [s].[schedule_id] = [s2].[schedule_id]
        WHERE   [enabled] = 1

Now in this code I have linked to the oldserver.sysjoschedules table so that I can determine whether the code is enabled or not as I only wanted to transfer the active schedules from the old server. You may or may not wish to do the same.

I hope this helps you while you are working with your servers. If you are in the US you may be interested in seeing some world class SQL Server speakers for free. RedGate are running the LA SQL in the City event there. Do try to get along, the London event was brilliant.