recreate agents jobs in MS SQL
After the server failure, I lost access to the database copies and to the configured agents. How to recreate agents jobs.
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.
A script that shows us where and 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 Jobsalso gets the category name and filters out the report server jobs.
and next example
SELECT sysjobs.name 'Job Name', syscategories.name 'Category', CASE [description] WHEN 'No Description available.' THEN '' ELSE [description] END AS 'Description' FROM msdb.dbo.sysjobs INNER JOIN msdb.dbo.syscategories ON msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id WHERE syscategories.name <> 'Report Server' ORDER BY sysjobs.name
Jobs are stored in the msdb database. You will have to restore this.