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








9 comments:

  1. Thank you ... works well for jobs and logins.

    ReplyDelete
  2. It's good that you write about it on your blog.
    paraprotex

    ReplyDelete
  3. This is also a very good post which I really enjoyed reading. It is not every day that I have the possibility to see something like this..
    Jobs

    ReplyDelete
  4. I adore your websites way of raising the awareness on your readers.fake packwoods

    ReplyDelete
  5. This definition contains the key concepts of providing for the needs of humanity and recognizing the opportunities and constraints of resource availability and use. California environmental jobs

    ReplyDelete
  6. Gangaur Realtech is a professionally managed organisation specializing in real estate services where integrated services are provided by professionals to its clients seeking increased value by owning, occupying or investing in real estate. getting a new job fast

    ReplyDelete
  7. After conducting a confidential research effort, a high-technology firm discovered that its primary competitor is gearing-up to target a new, growth market and literally abandon its traditional market and installed base. Salesforce interview questions and answers

    ReplyDelete