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/