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'


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
Scott Hodgin
Scott Hodgin
August 17, 2018 12:09 PM

Related Questions



Problem running SSIS package remotely using PoSH

Updated April 23, 2015 01:02 AM


creating index on big table(s)

Updated September 03, 2018 18:06 PM