How to backup the SQL Jobs Script

Backup of the SQL Server Agent jobs is important to ensure that we can recover and restore them in case of system failures or migrations.
Here are few steps we can follow to backup SQL Server Agent jobs:

Method 1: Using SQL Server Management Studio (SSMS)

Open SQL Server Management Studio (SSMS)
Connect to the SQL Server instance where our jobs are configured.
In the Object Explorer, expand the SQL Server Agent node.
Right-click on "Jobs" and select "Script Jobs as" > "CREATE To" > "File...".
Save the script to a file. This script will contain the SQL statements to recreate the jobs.

Method 2: Using T-SQL Script

Open SQL Server Management Studio (SSMS)
Connect to the SQL Server instance where our jobs are configured.
Open a new query window in SSMS.
Run the Following Script:

DECLARE @jobName NVARCHAR(128), @jobId UNIQUEIDENTIFIER;
DECLARE job_cursor CURSOR FOR
SELECT job_id, name
FROM dbo.sysjobs;

OPEN job_cursor;
FETCH NEXT FROM job_cursor INTO @jobId, @jobName;
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @jobScript NVARCHAR(MAX);
    SET @jobScript = (SELECT [msdb].[dbo].[sp_get_composite_job_info] @job_id = @jobId);
    PRINT @jobScript;
    FETCH NEXT FROM job_cursor INTO @jobId, @jobName;
END

CLOSE job_cursor;
DEALLOCATE job_cursor;

Method 3: Using Power Shell Script
Open the PowerShell script window.
Run the Following Script:

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null
$serverInstance = "MYSystem"

$server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $serverInstance

$jobs = $server.JobServer.Jobs 

$DateFolder = get-date -format yyyyMMdd
$BasePath = "D:\SQLJobs_Backup\$DateFolder"

If(!(test-path -PathType container $BasePath))
{
      New-Item -Path $BasePath -ItemType Directory
}

if ($jobs -ne $null)
{
	$serverInstance = $serverInstance.Replace("\", "-")
	ForEach ( $job in $jobs )
	{
		$ValidFileName = $job.Name -replace '[^a-zA-Z0-9]', ' '
		$FileName = $BasePath +"\"+ $serverInstance + "_" + $ValidFileName + ".sql"
		$job.Script() | Out-File -filepath $FileName
	}
}

Select any of the above method that best suits your needs and preferences.

Posted

in

, , ,

by

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *