Post reply

Export all SQL Agent jobs

  • January 11, 2017 at 9:30 am

    #320416

    Hello Expert,

    How to extract all the SQL Agent jobs (script) in a single go? Is there any standard script available to extract? I know that we can do it by each sql agent job but it takes longer if there are long list of SQL Jobs are available.

    Thanks

    Thanks.

  • January 11, 2017 at 10:17 am

    #1922001

    The key is, while in Management Studio, ensure that the "Object Explorer Details" window is showing (if not, add it from the View menu), then you can select multiple jobs to script (script job as) from the menu on the right instead of only being allowed one.

  • January 11, 2017 at 10:18 am

    #1922003

    You can do all your Agent jobs in one go fairly easy:

    1. Click on the "Jobs" section in the SQL Agent in SSMS

    2. Hit the F7 key on your keyboard (opens the Object Explorer Details)

    3. Highlight the jobs you want to export in the Object Explorer Details

    4. Right-click the highlighted jobs and "Script Job As..." like you would for a single job

    I recently had to do this migrating several servers to new installs.

  • January 11, 2017 at 10:23 am

    #1922006

    Thanks guys. Was thinking if any way to export using T sql script as I do that for triggers by writing my script.

    Thanks.

  • January 11, 2017 at 10:42 am

    #1922007

    SQL-DBA-01 (1/11/2017)

    Thanks guys. Was thinking if any way to export using T sql script as I do that for triggers by writing my script.

    I would expect you could craft up a script to export the jobs and make a create script in the process, using the msdb.sysjobs, msdb.sysjobsteps and msdb.sysjobschedules tables. The schedules would likely be one of the harder parts to generate a script for, though.

    MS for some reason stored the dates and times as INTs...

  • January 11, 2017 at 11:06 am

    #1922017

    Thanks much..

    How to use newline character so that after each statement I can get the "go" and the new statement will appear followed by a new line. Can you suggest?

    SELECT

    'EXEC msdb.dbo.sp_update_schedule

    @name = '''+NAME+''',

    @enabled = 0;GO'

    FROM msdb..sysschedules

    Thanks.

  • January 11, 2017 at 11:31 am

    #1922030

    When you insert line feeds like this, how it ends up being displayed can very depending on what tools you are using. I usually use carriage return + line feed as you end up with fewer display issues in different tools or if you paste results into notepad or use a similar text file editor. It's just done like:

    select 'line one' + CHAR(13) + CHAR(10) + 'line two'

    If you are using SSMS, change the output to text

    Sue

  • January 11, 2017 at 11:52 am

    #1922041

    Thanks Sue

    Thanks.

  • January 12, 2017 at 3:36 pm

    #1922440

    you just need the char(10) for a newline !
    (I use this all the time)

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • January 12, 2017 at 4:11 pm

    #1922445

    Rudyx - the Doctor - Thursday, January 12, 2017 3:36 PM

    you just need the char(10) for a newline !
    (I use this all the time)

    Not necessarily. It will burn you eventually. Notepad is an easy one to show how it is interpreted differently. In a query, do:

    select 'line one' + CHAR(13) + CHAR(10) + 'line two' + CHAR(10) + 'line feed only'

    And select Query then Results to file, execute, save it as a text file (or default rpt, doesn't matter) and open the file in notepad.
    It will be:

    line one
    line twoline feed only

    Then paste the results in here from the text file and the results will be:

    line one
    line two
    line feed only

    There are some other similar examples if you do a search on the two.

    Sue

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply