Copying, moving, backing up and/or restore SQL Agent jobs is a common task for a SQL Server administrator: in this post we'll see how it can be done for a single job or for multiple jobs at the same time. The technique can also be used to copy the jobs to another server, for example during a SQL Server database migration.
To perform these tasks we need to have SQL Server Management Studio, the official MS tool to manage SQL Server databases, which is available as an optional component of all SQL Server editions: if you don't have it installed, you can download it for free from this link.
Backup a single SQL Agent Job
- Start SQL Server Management Studio.
- Expand the SQL Server Agent folder, and then expand the list of Jobs.
- Right-click the job you want to create a backup script for, and then select Script Job as.
- Click CREATE To or DROP To, then click New Query Editor Window, File, or Clipboard to select a destination for the script. Typically, the destination is a file with a .sql extension.
- Repeat this procedure from Step 3 for each job you want to script.
Backup multiple SQL Agent Jobs
- Start SQL Server Management Studio.
- Expand the SQL Server Agent folder, and then expand the list of Jobs.
- Click to the View menu panel, then to Object Explorer Details (or press F7) to show the details window (see image below).
- The new details window should show a list of jobs which allows to select multiple jobs.
- Use Shift+Left-Click or CTRL+Left-Click to select the jobs to create a backup script for, then right-click on them and select Script Job as.
- Click CREATE To or DROP To, then click New Query Editor Window, File, or Clipboard to select a destination for the script that will contain all the job you want to backup. Typically, the destination is a file with a .sql extension.
Restore one or multiple SQL Agent Job
- Start SQL Server Management Studio.
- Click File, click Open, then click File.
- Locate and open the file containing the scripted job(s).
- Execute the script to create the job(s).
That's it.
This post is loosely based upon this official MSDN reference article, which lacks to describe the option to backup multiple jobs in a single task.
Thank you for the great post!
This is a fantastic solution!
I’m wondering though if there’s a way to automate this with another job to keep it up to date in case of any changes to the jobs as there are multiple people with access?