Import Agent job history from another server

  • Hello experts,

    I'm working on a SQL Server migration, and the clients just asked me to copy the job history from the old server to the new server.

    For some reason, no one has ever asked me to do this before, so when I was first setting up the new server, I just scripted out the jobs on the old server and created them on the new server. Now those migrated jobs have started adding their own job history.

    Does anyone know how I can import the job history records from the old server into the new msdb on the new server? I have modified the new jobs for the new environment, so I can't just restore the old msdb over the new one.

    Thanks for any help!

    -- webrunner

    • This topic was modified 6 days, 12 hours ago by  webrunner. Reason: Adding detail to a sentence

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • You can't restore an older version msdb over another msdb anyway, no matter what.

    You could, however, restore the msdb db to a different db name (e.g. msdb_old).

    I think you could then INSERT rows from the old msdb dbo.sysjobhistory table to the new msdb.dbo.sysjobhistory table.

    You might want to consider using SET IDENTITY_INSERT dbo.sysjobhistory ON; so you can use negative numbers for the old rows so you can tell them from the new rows, but naturally that's not a requirement.  You will want to look up the current job_id for a given job name so that it matches the job id on the new instance.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • Thanks so much, Scott. That is extremely helpful information. Much appreciated.

    -- webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • No problem.  Let me know if you need help on getting the correct job_id to add to the sysjobhistory table.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • Thanks, Scott. This is what I came up with. I decided not to use SET IDENTITY_INSERT dbo.sysjobhistory ON since they are just interested in having access to old history, not so much distinguishing the old from the new.

    The query is a little confusing because I used the alias 'o' but that is for the new job ID, which I substituted for the old history records to be inserted.

    Seems like it worked. I will check it from the GUI next.

    Thanks again.

    -- webrunner

     

    --===== Put old job IDs into a holding table.
    select o.job_id as old_job_id, j.job_id
    into dba.dbo.old_jobs
    from msdb_old.dbo.sysjobs o
    inner join msdb.dbo.sysjobs j on o.name = j.name

    --===== Checks.
    select * from msdb_old.dbo.sysjobs

    --===== Check for any job IDs that already exist in the new msdb.
    select * from [msdb].[dbo].[sysjobhistory]
    where job_id in (select job_id from [msdb_old].[dbo].[sysjobhistory])

    --===== Check: Pull old job history info using the old job IDs mapped to the new job IDs.
    select old_job_id, job_id
    from dba.dbo.old_jobs


    -- INSERT rows from the old msdb dbo.sysjobhistory table to the new msdb.dbo.sysjobhistory table.
    BEGIN TRANSACTION

    INSERT INTO [dbo].[sysjobhistory]
    ([job_id]
    ,[step_id]
    ,[step_name]
    ,[sql_message_id]
    ,[sql_severity]
    ,[message]
    ,[run_status]
    ,[run_date]
    ,[run_time]
    ,[run_duration]
    ,[operator_id_emailed]
    ,[operator_id_netsent]
    ,[operator_id_paged]
    ,[retries_attempted]
    ,[server])
    SELECT o.job_id
    ,h.[step_id]
    ,h.[step_name]
    ,h.[sql_message_id]
    ,h.[sql_severity]
    ,h.[message]
    ,h.[run_status]
    ,h.[run_date]
    ,h.[run_time]
    ,h.[run_duration]
    ,h.[operator_id_emailed]
    ,h.[operator_id_netsent]
    ,h.[operator_id_paged]
    ,h.[retries_attempted]
    ,h.[server]
    FROM [msdb_old].[dbo].[sysjobhistory] h INNER JOIN dba.dbo.old_jobs j ON h.job_id = j.old_job_id
    INNER JOIN msdb.dbo.sysjobs o ON o.job_id = j.job_id


    -- ROLLBACK TRANSACTION


    -- COMMIT TRANSACTION



    • This reply was modified 6 days, 6 hours ago by  webrunner.

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • This was removed by the editor as SPAM

Viewing 6 posts - 1 through 6 (of 6 total)

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