0

I want to make a few duplicate copies of an existing SQL Server Agent Job on the same server and edit them to do the same action on different databases that exist on the same server.

These are multiple step agent jobs which would be a bit more work to recreate manually. I know I can create a script from an agent job to run on a different server, but I don't think that will work on the same database server. How can I achieve this without recreating the agent job from scratch and without replacing the existing job?

Ultimately I want to end up with about 5 duplicates of the original job...

2

I'm certain that this isn't the most elegant solution, but it will work. You'll need the dbatools PowerShell module, which you can install from the PSGallery with install-module dbatools or via the other methods at https://dbatools.io/install

Once dbatools is installed, from a PowerShell prompt/console, run the following:

Get-DbaAgentJob -SqlInstance YOURINSTANCE -Job "YOUR_SOURCE_JOB_NAME" | Export-DbaScript -FilePath c:\tmp\MyJobToCopy.sql

You can then open up that SQL file in Management Studio, change every instance of YOUR_SOURCE_JOB_NAME to the "new" name you want the copy of the job to have, and execute the SQL. Lather, rinse, repeat for the other 4 copies you need.

This could definitely be better as a self-contained script to do the name changes and query execution all in one go. But for a one-off, this will get you where you need to go.

If you were copying this job out to 5 different instances with the same name everywhere, it'd be a lot cleaner & easier thanks to Copy-DbaAgentJob (also from dbatools).

| improve this answer | |
  • Would this cause problems with the script generated having the same schedule_uid and name as the original job? Should I edit the schedule creation statements out? – Benzo Apr 9 at 17:16
  • I think (been a while since I've done this) it will just put all the copies of the job on the same shared schedule. – alroc Apr 9 at 18:29
  • Looks like this worked fine for me. I was able to make multiple copies of the job, though, I did comment out the schedule creation commands so I could handle scheduling manually. – Benzo Apr 10 at 16:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.