How to copy, backup and restore one or multiple SQL Agent Jobs in SQL Server 2008-2017

SQL Server - Recuperare il Product Key da una installazione esistente

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

  1. Start SQL Server Management Studio.
  2. Expand the SQL Server Agent folder, and then expand the list of Jobs.
  3. Right-click the job you want to create a backup script for, and then select Script Job as.
  4. 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.
  5. Repeat this procedure from Step 3 for each job you want to script.

Backup multiple SQL Agent Jobs

  1. Start SQL Server Management Studio.
  2. Expand the SQL Server Agent folder, and then expand the list of Jobs.
  3. Click to the View menu panel, then to Object Explorer Details (or press F7) to show the details window (see image below).
  4. The new details window should show a list of jobs which allows to select multiple jobs.
  5. 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.
  6. 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.

How to copy, backup and restore one or multiple SQL Agent Jobs in SQL Server 2008-2017

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.

 

About Ryan

IT Project Manager, Web Interface Architect and Lead Developer for many high-traffic web sites & services hosted in Italy and Europe. Since 2010 it's also a lead designer for many App and games for Android, iOS and Windows Phone mobile devices for a number of italian companies. Microsoft MVP for Development Technologies since 2018.

View all posts by Ryan

0 Comments on “How to copy, backup and restore one or multiple SQL Agent Jobs in SQL Server 2008-2017”

  1. Pingback: SQL Server - copia, backup e ripristino di uno o più processi SQL Agent

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.