locked
SQL Agent Jobs RRS feed

  • Question

  • Dear Experts

    I need  your ideas and experience please. i have a SQL agent jobs that are running in my current DW(data warehouse). we have a new Prodcution DW right now and my job is to move those jobs to the new DW. My question is what is the practice or experience on such migration. There are many jobs that are configured in so many different ways and i just need an idea please? thank you as usual.

    Wednesday, February 5, 2014 2:55 PM

Answers

  • Hello,

    You can script out all jobs using SSMS: Do a right mouse click on a job => "Script as " => "Create script". Then you get a complete script, which you can execute on the new server to create the job there.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Tom Phillips Wednesday, February 5, 2014 5:12 PM
    • Marked as answer by tracycai Thursday, February 13, 2014 10:19 AM
    Wednesday, February 5, 2014 5:09 PM

  • If you want to script all the jobs - You can refer this link 

    http://learnmysql.blogspot.com/2012/05/script-all-jobs-in-sql-server.html

    You can also do with Powershell.

    function script-SQLJobs([string]$server,[string]$outputfile) 
    { 
        [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null 
     
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server("$server") 
        $db = New-Object Microsoft.SqlServer.Management.Smo.Database 
        $scrp = New-Object Microsoft.SqlServer.Management.Smo.Scripter($srv) 
        $scrp.Options.ScriptDrops = $FALSE 
        $scrp.Options.WithDependencies = $TRUE 
     
        $jobs = $srv.JobServer.get_Jobs() 
         
        foreach($job in $jobs) 
        { 
            $script=$job.Script() 
            $script >> $outputfile 
            "GO" >> $outputfile 
     
        } 
    } 
    
    $server=Read-Host " Enter the server name"
    $OutputFile = Read-Host " Enter the Output File Path"
    
    script-SQLJobs $server $outputfile

    --Prashanth

    • Marked as answer by tracycai Thursday, February 13, 2014 10:19 AM
    Wednesday, February 5, 2014 5:42 PM

All replies

  • Hello,

    You can script out all jobs using SSMS: Do a right mouse click on a job => "Script as " => "Create script". Then you get a complete script, which you can execute on the new server to create the job there.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Tom Phillips Wednesday, February 5, 2014 5:12 PM
    • Marked as answer by tracycai Thursday, February 13, 2014 10:19 AM
    Wednesday, February 5, 2014 5:09 PM

  • If you want to script all the jobs - You can refer this link 

    http://learnmysql.blogspot.com/2012/05/script-all-jobs-in-sql-server.html

    You can also do with Powershell.

    function script-SQLJobs([string]$server,[string]$outputfile) 
    { 
        [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null 
     
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server("$server") 
        $db = New-Object Microsoft.SqlServer.Management.Smo.Database 
        $scrp = New-Object Microsoft.SqlServer.Management.Smo.Scripter($srv) 
        $scrp.Options.ScriptDrops = $FALSE 
        $scrp.Options.WithDependencies = $TRUE 
     
        $jobs = $srv.JobServer.get_Jobs() 
         
        foreach($job in $jobs) 
        { 
            $script=$job.Script() 
            $script >> $outputfile 
            "GO" >> $outputfile 
     
        } 
    } 
    
    $server=Read-Host " Enter the server name"
    $OutputFile = Read-Host " Enter the Output File Path"
    
    script-SQLJobs $server $outputfile

    --Prashanth

    • Marked as answer by tracycai Thursday, February 13, 2014 10:19 AM
    Wednesday, February 5, 2014 5:42 PM