Search
SQL Server Expert Blogs
Preserving SQL Job History
I am often migrating servers and like to keep the SQL job history more accessible than a backup I need to restore. So, I created a script for saving this information into a table for easy access later:
--Insert into SavedJobHistory --(If table schema created already)
SELECT h.server, j.Name, j.enabled, j.description, j.start_step_id, j.date_created, j.date_modified, h.step_id, h.step_name, h.message, h.run_status, h.run_date, h.run_time, h.run_duration, sc.next_run_date
--into SavedJobHistory --(if table schema not created yet.)
FROM sysjobs j
LEFT JOIN sysjobhistory h ON j.job_id=h.job_id
LEFT JOIN sysjobschedules sc ON j.job_id = sc.job_id
WHERE (run_date IS NULL OR run_date > '20080101')
AND j.Name NOT LIKE '%-host-%' --ignore replication jobs
AND j.name NOT LIKE 'qcm%' --quest tool job.
AND j.name NOT LIKE 'backup%' --ignore backups
AND j.name NOT LIKE 'lsalert%' --ignore logshipping
AND j.name NOT LIKE 'lsbackup%' --ignore logshipping
AND j.name NOT LIKE 'DBA:%' --ignore dba monitoring jobs
-Chuck Lathrope
@SQLGuyChuck
