Import Agent job history from another server

Question

Post reply

Import Agent job history from another server

webrunner

SSC-Dedicated

Points: 31128
More actions
August 4, 2022 at 3:12 pm
Go to Answer
#4072508
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
ScottPletcher

SSC Guru

Points: 100375
More actions
August 4, 2022 at 4:21 pm
Answer
#4072555

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!

August 5, 2022 at 11:07 am

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

webrunner SSC-Dedicated Points: 31128 More actions · Answer 1

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

ScottPletcher SSC Guru Points: 100375 More actions · Answer 2

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!

webrunner SSC-Dedicated Points: 31128 More actions · Answer 3

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