Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 948
Std_Net Avatar asked Oct 08 '12 17:10

Std_Net


3 Answers

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

like image 130
Tim Lehner Avatar answered Oct 14 '22 11:10

Tim Lehner


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.

like image 30
Pete Carter Avatar answered Oct 14 '22 12:10

Pete Carter


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.

like image 31
it3xl Avatar answered Oct 14 '22 12:10

it3xl