page contents

About the Post

Author Information

Have a need to port existing SQL Server Agent Jobs?

If you are like me, you have a few SQL Servers that you are managing and sometimes you write a cool job and say to yourself that would be awesome if I could take this job on Server A and allow Server B to run this job as well.

Well, a good friend of mine (Jeremy Carter of jeremycarterblog.com) taught me how to do this task and I want to pass this information onto you as well. This feature is another benefit of doing a job versus a maintenance plan and once again automation helps save the day 🙂

Here is a screen shot of my SQL Server Agent Jobs:

I got 3 jobs here that I most focused on the (Backup DB’s, BU Tlogs and Clean up backup jobs) Currently, I have 2 SQL jobs that backup all of my databases (Backup DB’s) and another job that backs up my transaction logs. (BU Tlogs) The job also puts each backup job into its own folder, so database 1 is in its own unique folder, which is separate for database 2. As you can guess, if I did not check up on this drive, then I would quickly run out of space and it can be pretty tedious to have to manually perform this task all the time of removing older jobs. So, I created a third job to clean up any jobs that were past my retention period so ensure that I had space for new backups.

Here is that recent job that I created, that goes ahead and cleans up my backup jobs automatically, so lets walk through how you export it. First off, right-click on the job and select Script Jobs as, Create to, New Query Editor Window (Noted below)

This will cause the job to be automatically scripted out for you, as you can see below, however we are not done with the task.

 

We will still need to make some changes to ensure that the script can run on the other server. Most of the time, we are creating the job as the user we are logged in to SQL Server and that account may not be available on the other SQL server we are wanting to migrate this job. Also, we have called out this particular SQL server in our job and we will need to change it. Here are those areas to look for:

In this case, you would have to edit either the Server or Domain name and then the correct user. Last but not least, you want to check on the server field to see if you are calling out a server by name or if you did what I did and just told SQL agent to run the job on the local server. (Both fields are highlighted in blue for you)

After you make all the appropriate changes, then you can save that script and then run in on other servers in your environment to port these other “cool” SQL Server agent jobs that you have created without having to start from scratch. 🙂

This is also helpful in the event you have to rebuild a SQL server that died expectantly and help you get it back up fully a little faster.

——————————————————————————————


Tags: , , , ,

Comments are closed.

Copy Protected by Chetan's WP-Copyprotect.