Script to get all the jobs in SQL Server from SQL Server Management Studio
This is very interesting fact that people always look for shortcuts especially while accomplishing the big tasks. Here i have came up with a scenario: I have got a task to setup a new server B (lets assume) exactly as a replica of Existing Server A.
It involve a lot of steps in which there is a step to copy all the EXISTING JOBS FROM SERVER A to SERVER B (Please note i have written All the Jobs).
Here is a easiest way i have found which might be helpful for many people.
Get the list current Jobs in SQL Server:
Step1: Enable Object explorer detail.
Step2:In SQL Server 2005 or earlier: goto view->Summary
In SQL Server 2008 or older: goto view-> Object Explorer Details
Step3:
Expand SQL Server Agent and click on Jobs.
All the jobs will be visible in summary/Object Explorer Detail window. (See Screenshot).
Step4:Here you can select all the jobs or the desired jobs which you want to script out.
After select right click -> Script Job As -> Create To -> and select the desired location where you want to script all the selected jobs (In the screenshot i have selected New Query Editor Window).
Note: The above screenshots/Examples are of SQL Server 2008R2 you can follow the same steps with SQL Server 2005 or earlier versions
Here is a easiest way i have found which might be helpful for many people.
Get the list current Jobs in SQL Server:
SELECT js.server,js.database_name,js.database_user_name,j.enabled,j.name, js.step_name, js.command,
j.date_created, j.date_modified,
js.last_run_date,js.last_run_duration,js.last_run_outcome
FROM msdb.dbo.sysjobsteps js WITH(NOLOCK)
LEFT JOIN msdb.dbo.sysjobs j WITH(NOLOCK)
ON j.job_id = js.job_id order by name
Output:
server database_name database_user_name enabled name step_name command date_created date_modified
last_run_date last_run_duration last_run_outcome
AUZUDC6DD SCDistribution NULL 1 Agent history clean up: SCDistribution Run agent. EXEC dbo.sp_MShistory_cleanup @history_retention = 48 2012-11-21 14:29:27.410 2012-11-21 14:29:27.417 20140123 0 1
|
Step2:In SQL Server 2005 or earlier: goto view->Summary
In SQL Server 2008 or older: goto view-> Object Explorer Details
Step3:
Expand SQL Server Agent and click on Jobs.
All the jobs will be visible in summary/Object Explorer Detail window. (See Screenshot).
After select right click -> Script Job As -> Create To -> and select the desired location where you want to script all the selected jobs (In the screenshot i have selected New Query Editor Window).