Migrate SQL Server Agent job to a new server

Monitoring SQL Server Performance

Migrate a SQL Server Agent Job from a server to another is a task that is performed quite often.

There can be several reasons to do that. I can list some of them. First one that comes to my mind, by personal experience, is when we need to align a test and a quality system to perform some additonal checks before going to production.

The task of manually creating an SQL Server Agent job from scratch can be overwhelming and error prone. This is the case especially when the number of jobs to migrate relevant. The best option is to script the job that you want to migrate. I will show you how to do it, with a step by step guide.

Just for clarity, in this example I am using Microsoft SQL Server Management Studio 2017, but I can assure you that the interface is going to be pretty similar also for other and more recent versions of SSMS.

First thing to do is to connect to the source system and then locate the SQL server Agent job that you want to migrate.

After that you right-click on the job and select “Script Job as” -> CREATE To -> New Query Editor Window”.

A new query window will open already prefilled with the T-SQL script. Last step is now to change the connection. Right-click in the query window and then select “Connection -> Change Connection…” and connect to the destination database.

Last step is to excute the script. The job is now being migrated to the other server. To double-check the outcome of the script you can connect now to the destination server’s SQL Server Agent. There you will now be able to see the migrated job in the jobs folder.

I hope this guide has been helpful.

Be the first to comment

Leave a Reply

Your email address will not be published.