SQL Server 2005 – How to Save Multiple SQL Jobs in Single Files

jobssql serversql-server-2005

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?

Best Answer

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() }

}

Related Question