Results 1 to 4 of 4

Thread: Scripting Jobs

  1. #1
    Join Date
    Feb 2002
    Posts
    28

    Unanswered: Scripting Jobs

    Hi,
    I have about ten jobs running under SQL Server agent on my server. I want to create a script file of three of the jobs to restore on to a different server. I selected all the three jobs -> right click -> generate SQL script. In the script generated, there was only one job scripted. The script was not created for all the three jobs but for only one job. Do I have to generate a script file for each job that I have on my server? Can I not generate a single script file for all the three jobs?

    Thanks
    P.C. Vaidyanathan

  2. #2
    Join Date
    Jun 2002
    Location
    INDIA
    Posts
    1

    Scripting

    Hi

    I dont think we can script all three jobs at a time.

    Thanks
    vasanth

  3. #3
    Join Date
    May 2002
    Location
    Montréal, Canada
    Posts
    28
    That's right (and bad!).

    You have another option, right click on the 'Jobs' element in EM, you can script ALL of them - but can't select only the one you want! Then you have to eliminate by hand in your file those you don't want...
    Data Climber

  4. #4
    Join Date
    Jun 2002
    Location
    Belgrade
    Posts
    10

    Thumbs up

    Or make new Job to script all of your jobs on SQl Server.

    Step 1.
    ActiveX Script >> set to >> VB Script :

    ============================

    Dim oSQLServer
    Dim oStream
    Set oSQlServer = CreateObject("SQLDMO.SQLServer")
    Set oStream = CreateObject("ADODB.Stream")
    oSQLServer.Connect "MySERVER", "UserID", "Pwd"

    Dim idStep
    Dim ScriptJob
    Dim CountJobs
    Dim JobName
    Dim ScriptAllJobs

    For Each oJob In oSQLServer.JobServer.Jobs
    CountJobs = oSQLServer.JobServer.Jobs.Count
    Next

    For idStep = 1 To CountJobs

    JobName = oSQLServer.JobServer.Jobs.Item(idStep).Name
    ScriptJob = oSQLServer.JobServer.Jobs.Item(idStep).Script(4, "C:\" & JobName & ".sql")

    ScriptAllJobs = ScriptAllJobs & ScriptJob

    Next

    oStream.Open
    oStream.WriteText (ScriptAllJobs)
    oStream.SaveToFile ("C:\SQLAllScripts.sql"), 2
    oStream.Close
    oSQLServer.DisConnect

    Set oStream = Nothing
    Set oSQLServer = Nothing

    =====================================

    First Loop will make script for each Job ,and second one for all of them .
    Schedule this Job , on the daily basis and backup your Jobs.

    regards.
    srdjan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •