Easiest way to move SQL Agent Job to another server

I have learned that it is not always about “doing it yourself” rather it is about — “has anyone already solved the problem” or is there an easier of doing things. In most cases there always is someone who has done it before. While, I was working on automating a task to be able to move multiple jobs on demand from one server to another I didn’t know about dbatools.io , yes and if you didn’t know it until now you are in for a treat.

I spent hours trying to write queries, powershell code and make them work together. Which wasn’t hard but rather time consuming and I don’t like wasting time. Hence enters DBATools, which will save you ton’s of time to be able to move one or as many jobs dynamically. Here is how I used it and you can re-purpose it for your needs

Requirements : Pre-installed DBATools (Download link https://dbatools.io/download/) , Powershell

I pulled below code from my powershell script. This lives in a loop where I have multiple jobs that are being moved from one ServerA to ServerB

Copy-DBAAgentJob -Source $server -Destination $targetserver -Job $jobname

Once you have copied over all jobs , you can utilize Remove-DBAAgentjob command to remove jobs from source server if needed.

Remove-DBAAgentJob -SQLInstance $server -Job $jobname

For more details and usage details go to https://dbatools.io/commands/

Originally published at https://blog.sanchitwadhwa.com.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Sanchit Wadhwa

I help solve data problems |Need help ? email me > imsunchip@gmail.com | website > sanchitwadhwa.com | ❤ Books, music, dance, food, podcasts, soccer, writing