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/

  • January 9, 2023