Today I have to copy some SQL Server Agent Jobs from the Dev database to the test database. Hmmm, I have got a lot of experience with SQL server agent jobs
I right clicked on the jobs and saw they were treated like sql stored procedure. When you right clicked the job you can then choose Script Job as and then save CREATE To and then you can choose to either save it to a File and or New Query Editor Window.
So basically I saved it to a new query window, I then opened a query window on the Test database, ran the query and whammo, I had copied the job across.
I would like to add a word of warning, in my instance a lot of variables were still pointing at the old server, file directories and connections. So you will have to be careful here, especially if you are doing this from test to live but of course you lto know that already.
if you want step by step instructions then I found this good article below which you can find here. Although the article uses SQL Server 2005, it’s the same process I used on SQL server 2008
How To Copy SQL Server 2005 Jobs From One Server To Another
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.
Hosk's Dynamic Blog 