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
-------------------
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) A socialist is someone who will give you the shirt off *someone else's* back.
August 4, 2022 at 4:23 pm
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
August 4, 2022 at 4:51 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
August 4, 2022 at 8:32 pm
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
-------------------
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
August 5, 2022 at 11:07 am
This was removed by the editor as SPAM
August 12, 2022 at 5:50 am
Part of your job is to tell management what is possible and what is not.
You may have a technical solution that merges job history, but it requires you to manipulate system tables and this is not supported by Microsoft. If you go ahead your client needs to know this and sign off on the risk.
The client could be given an alternative of some queries they can run to see the old history, with a plan to drop the copy of the old msdb after 60 days. This gives no MS support issues and I know which option I would choose to minimise risk.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 12, 2022 at 1:34 pm
Thanks Ed, that is an excellent reminder. I too often focus on doing what is asked of me. Sometimes the answer has to be "That is not possible," or "That is not possible, but here is what we can do instead."
Thanks again.
-- 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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply