Wednesday, August 7, 2013

How to script all the SQL Agent Jobs in a single script file

Background
The purpose of this article is to provide a way of scripting out all the SQL Agent jobs in a single script file. If we simply go to SQL Server Agent node then Jobs folder in management studio for scripting all jobs or more than one jobs then management studio does not allow selecting more than one job for scripting.


Fig- showing user can select only one job at a time for scripting

Steps to script out all the Jobs available in an instance of SQL Server
We are going to use Object Explorer Details property that shows the objects with more details and allows selecting all the jobs together for creating the script.

  •   Go to SQL Server Agent Node then Jobs. It will enlist all the SQL Agent jobs:    

  • Go to View menu and select Object Explorer Details.
  • It will enlist all the jobs in separate pane with more details:

  • Select all the jobs using Ctrl + A then right click on the selected jobs then Script Jobs as

    It will script out all the Jobs in a single Query window or file.


    Conclusion :
    By using Object Explorer Details in management studio, we can script out all the Jobs available in an instance of SQL Server. Same solution can be utilized for below requirement also:
  • How to script all the Linked Servers in a single script
  • How to script all the logins in a SQL Server Instance








No comments:

Post a Comment