Post reply

Copying Individual Jobs From Live to Test Server

  • October 11, 2006 at 9:08 am

    #62446

    We need to refresh a test server nightly with database and related jobs from a live server.  The test server also has other databases and jobs that we do not want to disturb.  Therefore, we can't just restore MSDB.  Here's what I propose; is it totally insane?  Whaddaya think???
     
    CHALLENGE:

    1. There will be jobs we want to transfer to TEST nightly, replacing the corresponding job.
    2. There will be jobs we do not want to transfer, but rather leave the corresponding job alone, because it points to different servers or functions differently due to the different environment.
    3. There will be jobs that exist on TEST that don't exist on LIVE and we don't want to disturb, such as jobs that affect other databases, and those that do server maintenance.

    RECOMMENDATIONS:

    1. We'll flag all jobs that are appropriate to transfer nightly with a unique job category.
    2. I'll write a script that will delete all jobs in that category from the appropriate tables on TEST and then copy them from LIVE, replacing the "originating_server" column.  ('job_no' is a uniqueidentifier, so it can be copied as is.)

    The relevant tables as far as I can see are these:
    /****** TABLES ******

    sysjobs
    sysjobsteps
    sysjobservers
    sysjobschedules
    sysjobhistory

    sysalerts

    systaskids
    ********************/
    What say you all?


    ALL Reality is Virtual...

  • October 11, 2006 at 9:33 am

    #665253

    Roger,

    It sounds prefectly feasible but I would suggest stearing clear from writing scripts against your system databases.

    Have you investigated the use of the 'SQL Server Agent Procedures' BOL: System Stored Procedures (Transact-SQL Reference)?

    You have sprocs such as:

    sp_add_job, sp_add_job_step and sp_add_jobschedule.

    You could then make a much cleaner solution such as follows:

    • Create a custom database and store your job dexcriptions, step scripts, schedules etc in a table
    • Create a scheduled job that steps through this table creating the jobs on your Production (live) Server.
    • Create a similar scheduled job that steps through this table creating the jobs on your Test server as requried.  

    I can go into more depth if you want to follow that kind of route.

    ll

     

     

  • October 11, 2006 at 10:11 am

    #665263

    Thanks, but that seems a little cumbersome when the jobs already exist on the production server and are subject to change from day to day.

    What am I missing regarding the relationships and operation of MSDB tables?


    ALL Reality is Virtual...

  • October 12, 2006 at 2:25 am

    #665404

    I wouldn't say cumbersome is the word, I would say maintainable, stable and also upwards compatible if you plan to upgrade to 2005.  How long do you want this solution to last?  I would really enforce again that you shouldn't be updating/querying the system tables directly, especially when such a thorough set of system stroed procedures are available.

    How are you planning to script the jobs out of the Production server?  I have done this before when moving to a new serevr and didn't find it a particulalry clean way to create jobs on the target server.  I ended up re-scritping the jobs using the system stored procedures and keeping the scripts in source control, that way I can always re-create them if need be on any server by substituting a few variables.

    ll

  • October 12, 2006 at 10:27 am

    #665549

    II,

    OK, I'd really prefer to let you convince me.

    So, how do I use system stored procedures to script the jobs out of the production server into tables?  Even if I could capture the result set from sp_help_job, its columns don't exactly match the parms of sp_add_job, so I'd have to use dynamic SQL to explicitly equate the columns to the parms... and these will undoubetely change in SQL2005.

    Here's what I was planning to do (after deleting from the test server)...

    ______________________________________________

    select

    * -- No need to know current column structure

      into #jobs_transfer

      from puccini.msdb.dbo.sysjobs -- Production Server
     where category_id=101          -- Transferrable Jobs

     
    insert

    holst.msdb.dbo.sysjobs   -- Test Server

    select

    * from #jobs_transfer

     
    update

    jbs

       set originating_server=N'HOLST'

          ,owner_sid=(select sid

                        from holst.msdb.dbo.sysusers
                          where name='dbo')
          ,enabled=0 -- We don't want them to run just now
      from holst.msdb.dbo.sysjobs jbs
      join #jobs_transfer         trn on trn.job_id=jbs.job_id
    _______________________________________________
     
    ...and so forth with sysjobsteps, sysjobschedules and sysjobservers.
    ______________________________________________________
     
    insert holst.msdb.dbo.sysjobsteps
    select stp.*
      from puccini.msdb.dbo.sysjobsteps stp
      join #jobs_transfer               jbs on jbs.job_id=stp.job_id
    _______________________________________________
     
    I really appreciate the time you're taking with me on this.

    Roger


    ALL Reality is Virtual...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply