One-off Backup and restore of all SQL Server Agent jobs using SQL Server Management Studio
1. Connect to the SQL server using SQL Server Management Studio.
2. Navigate to the Object Explorer Details view by selecting View à Object Explorer Details from the menu bar or press F7.
3. Double-click SQL Server Agent from the Object Explorer Details window.
4. Double-click the Jobs folder to expand the list of agent jobs currently configured on the SQL Server.
5. At this point you have the option to select only the jobs you want to backup, or select all jobs on the server (Ctrl+A).
6. Once your selection has been made, right-click over the selected area, hover Script Job As à CREATE To à File …
7.Navigate to a file location and save your SQL Server Agent job(s) creation code to a .SQL file.
Open the SQL file to execute on another SQL Server to restore a previous version of agent job(s), or to create the job(s) at a new location.
This example shows how to copy a scheduled backup job from one instance of SQL server 2005 to another.
In Microsoft SQL Server Management Studio, expand the SQL Server Agent tree.
Expand the jobs tree.
Here you will see a list of all the jobs currently stored on the server.
Right-click on the job that you want to copy over to a second server.
Select Script Job as –> Create To –> New Query Editor Window.
This will open a new window in the management studio containing auto-generated code.
Connect to the Second server, which will be the destination for the copied job.
Open up a new tab in the management studio – Copy the entire auto-generated code from the previous step and paste it into the new tab.
Below is the auto-generated code ready to execute on the new server.
Execute the whole code to continue.
Refresh and expand the Jobs tab on the second server, all being well the copied job will be displayed in the job list.
Right-click the job and select Properties to view the details.
You will see that the steps, schedules, alerts, notifications etc. will have been copied across. The job will now run on server 2 in the same way as it ran on server 1.