4

I'm using SQL Server 2005 and I need to save every SQL agent job in single 'create in' *.sql files. The problem is that I got about 20 SQL Server with 15 (up to 51) SQL jobs.

How can I export these jobs easy in single files?

If I mark all jobs they will be saved in a single file. It would take to long to manually export every single job to a file.

Did someone have an idea?

  • Are you needing a single file for each server's jobs, or a single file for ALL of the jobs on all of your servers? Also, what do you intend to do with this? – DForck42 Jan 15 '15 at 22:04
  • 1
    I need a single file for each server's jobs because the jobs were never in QA and it was decided to make an QA. Now I have to checkin the jobs in CVS. – hellskitchen Jan 16 '15 at 11:20
1

In one of our processes we are using Powershell to achieve this.. I am not sure of the link who has written, since it resides in our process, but below script might be useful to you.

param([String]$ServerListPath) 

#Load the input file into an Object array

$ServerNameList = get-content -path $ServerListPath 

#Load the SQL Server SMO Assemly

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null 

#Create a new SqlConnection object

 $objSQLConnection = New-Object System.Data.SqlClient.SqlConnection 

#For each server in the array do the following

foreach($ServerName in $ServerNameList)

 {
 Try
 {
     $objSQLConnection.ConnectionString = "Server=$ServerName;Integrated   Security=SSPI;" 
         Write-Host "Trying to connect to SQL Server instance on $ServerName..." -NoNewline
        $objSQLConnection.Open() | Out-Null
        Write-Host "Success."
    $objSQLConnection.Close()
}
Catch
{
    Write-Host -BackgroundColor Red -ForegroundColor White "Fail"
    $errText =  $Error[0].ToString()
        if ($errText.Contains("network-related"))
    {Write-Host "Connection Error. Check server name, port, firewall."}

    Write-Host $errText
    continue
}

#IF the output folder does not exist then create it

$OutputFolder = ".\$ServerName"
$DoesFolderExist = Test-Path $OutputFolder
$null = if (!$DoesFolderExist){MKDIR "$OutputFolder"}

#Create a new SMO instance for this

$ServerName
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $ServerName

#Script out each SQL Server Agent Job for the server

$srv.JobServer.Jobs | foreach {$_.Script() + "GO`r`n"} | out-file ".\$OutputFolder\jobs.sql"

#Use the command below to output each SQL Agent Job to a separate file. Remember to comment out the line above.

#Removed backslash character, typically seen in Replication Agent jobs, to avoid invalid filepath issue

#$srv.JobServer.Jobs | foreach-object -process

{out-file -filepath $(".\$OutputFolder\" + $($_.Name -replace '\\', '') + ".sql") -inputobject $_.Script() }

}

  • +1 looks like what I want. Unfortunately Powershell is deactived for the sql server but I will look to activate it. – hellskitchen Jan 16 '15 at 11:23
  • @hellskitchen, hope it helps and kindly markup if answer is useful or correct if answered, you're welcome ! – KASQLDBA Jan 16 '15 at 14:44
8

There are two possible ways
1. Automatically create scripts for all SQL Server Jobs

  1. I. Open Object Details

enter image description here

II. Select All Jobs (Shift + Mouse Click) -> Right Click -> Script Job as
enter image description here

  • Love the feature in your 2nd answer - can never work out why it's "hidden" behind using Object Explorer Details +1 – MHSQLDBA Jan 15 '15 at 13:13
  • 1
    @MHSQLDBA Because the Object Explorer tree does not support multi-select. – Aaron Bertrand Jan 15 '15 at 14:29
  • I meant I can't work out the reason for not making it a feature on the Object Explorer tree - is there a good reason that it does not? – MHSQLDBA Jan 15 '15 at 16:11
  • thanks, but in that way I only get a single file for all jobs not multiple files. – hellskitchen Jan 16 '15 at 11:21
3

I use Bill Graziano's Script SQL Server Configuration project on Codeplex for this. It can script the whole configuration of a SQL Server instance to .sql files, including SQL Server Agent jobs.

It is a command line utility, so with multiple servers to script, you can use a batch or PowerShell script to loop through all the servers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.