Where does SQL Server Agent save jobs?

Question

My server is down and I can only get the harddisk from it. I found my database and copied it but where I can find agent jobs? Where are they saved?

Automatic

Answer 1

Jobs are stored in the msdb database. You will have to restore this.

Juanjuana

Lehnerwhen I just replace files for this db any settings can change? – Automatic

If you are bringing this stuff up on a new server, I would probably restore MSDB to a new database ("MSDB_restore") and then script the jobs out of that to the new one. – Juanjuana

Answer 2

Within the MSDB database, jobs are stored in a tables called dbo.sysjobs. This joins to a table called dbo.sysjobsteps that stores details of the individule steps. The schedules are stored in dbo.sysjobschedules and the History is stored in dbo.sysjobhistory.

MSDB will also contain other instance level objects such as alerts, operators and SSIS packages.

Psychosomatics

Answer 3

Let me present the following brilliant SQL query to show us where & how SQL Server stores SQL Jobs.

-- List of all the SQL Jobs on a server with steps
SELECT
     job.job_id,
     notify_level_email,
     name,
     enabled,
     description,
     step_name,
     command,
     server,
     database_name
FROM
    msdb.dbo.sysjobs job
INNER JOIN 
    msdb.dbo.sysjobsteps steps        
ON
    job.job_id = steps.job_id
WHERE 1=1
    --AND job.enabled = 1 -- uncomment this to see enabled SQL Jobs

Also I remove "msdb." prefixes inside the query to see SQL Jobs from a msdb database restored from a backup.

Dunant