CODEWITHSUNDEEP
sqlsql-serverrestoresql-server-agent
Std_Net
Std_Net

Reputation: 1096

Where does SQL Server Agent save jobs?

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?

Upvotes: 10

Views: 31765

Answers (3)

it3xl
it3xl

Reputation: 2672

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.

Upvotes: 4

Pete Carter
Pete Carter

Reputation: 2731

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.

Upvotes: 20

Tim Lehner
Tim Lehner

Reputation: 15251

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

Upvotes: 16

Related Questions