Script SQL Server Agent jobs to file using bcp

by Vorster Last Updated August 17, 2018 12:06 PM

I am busy making a backup of all Agent Jobs by scripting them all to file.`How would i rewrite the below to use BCP instead

        BEGIN
        SET @EchoCommand = 'ECHO ' + @EchoField + ' >> ' + @Path
        + @JobName
        + REPLACE(REPLACE(CONVERT(VARCHAR(200), GETDATE()), ' ',
        ''), ':', '') + '.sql'

Something similar to the below

        SET @EchoCommand = 'bcp 
        "exec['+DB_NAME()+'].dbo.CreateBackUpFromJob' +
        @JobName + '" queryout "\\ServerName\SQL_Backups\' + @JobName + 
        '.sql" -c -UTF8 -T -SLOCALSERVER'

Tags : sql-server sql sql-server-2012 sql-server-2008

Answers 1

It's very easy to script out SQL Server Agent Jobs using Powershell (if that is an option). The following is an example of how you can use Powershell to script the Agent Jobs and also delete previous scripted jobs that were created over a number of days ago. This script generates a file per Agent Job

You can play around with the code to see if it meets your needs.

# Set sql server 
$Instances = "localhost"

foreach($Instance in $Instances)
    {
    # Create sql server and db objects
    $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $Instance;


    # Write out the job names 
    #$srv.JobServer.jobs|foreach-Object {Write-Host "$_.name" }


    # Script out the Jobs
    $CurrentDate = get-date -f "yyyy-MM-dd"
    $Instance = $Instance.Replace("\","-") + "-" + $CurrentDate
    $srv.JobServer.jobs|foreach-Object {$_.script() > "C:\Temp\SqlJobs\$Instance-$_.txt"}
    }


# Purge files older than 2 weeks
$cutoff = (get-date).AddDays(-14)
dir "C:\Temp\SqlJobs\"|Get-ChildItem -incl '*.txt' -rec|Where{$_.LastWriteTime -lt $cutoff -and !$_.PSIsContainer}|remove-item