Where are SQL Jobs Stored? – Interview Question of the Week #293

Question: Where are SQL Server Agent jobs stored? Job definitions are in msdb, with their main records in msdb.dbo.sysjobs. A deleted job is not kept there in an undelete folder.

A reader asked this on my YouTube channel because a job had been deleted accidentally. The database name answers where to look for existing jobs. It does not by itself recover the deleted definition. That second part is why I keep reminding clients to back up the system databases as well as their user databases.

List the jobs that still exist

SELECT job_id, name, enabled, date_created, date_modified
FROM msdb.dbo.sysjobs
ORDER BY name;

Job steps, schedules and execution history are stored in other related msdb tables, such as sysjobsteps, sysjobschedules, sysschedules and sysjobhistory. History describes executions; it is not a substitute for a complete script of the job’s commands and settings.

Recover a definition, not an entire production msdb casually

First look for a saved CREATE script, deployment definition or earlier system-database backup. Recover the earlier msdb state on a separate compatible recovery instance and script the missing job using supported tools. Restoring an old msdb directly over production would also roll back other jobs, schedules and history.

Before recreating it, review its owner, credentials or proxies, commands, target databases and schedule. Keep a recovered job disabled until those details are checked. Do not write directly to SQL Server’s system tables.

TempDB is different: it is recreated at service startup and is not backed up. Its creation time can also give you a quick clue to the last SQL Server service startup, rather than the last Windows restart. That exception does not remove the need for backups of msdb, master and model. Restrict access to msdb because job definitions may contain sensitive operational details.

Here are my related job queries and demonstrations:

• SQL SERVER – Details About SQL Jobs and Job Schedules
• SQL SERVER – Query to List All Jobs with Owners
• How to List All the SQL Server Jobs When Agent is Disabled?
• SQL SERVER – T-SQL Script to Check SQL Server Job History
• SQL SERVER – Displaying SQL Agent Jobs Running at a Specific Time
• How to Schedule a Job in SQL Server?
• Activity Monitor and Running Query – SQL in Sixty Seconds #115
• Email an Execution Plan – SQL in Sixty Seconds #114
• Execution Plan – Estimated vs Actual – SQL in Sixty Seconds #113

The sysjobs reference documents the definition table. The msdb guidance recommends keeping its backup current after job changes.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.