Saturday 30 April 2016

Reusing SQL Job creating script to create new similar ones with different Schedule ID

As we now it is very important to look for new ways of being more productive every day. For instance, one of our tasks as DBA is to implement SQL Backup Jobs for each database. Personally, I like reusing code to create more similar SQL Jobs faster, that is, create one SQL Job, generate the SQL creating script of it, replace some things, and finally execute it to create every SQL Backup Job for all databases.
After creating the next SQL Jobs by reusing the complete code, you will find that these SQL Jobs have the same SQL Schedule ID. So, if we modify the schedule for one of them, every SQL Job will be modified as well. Under this circumstance, we will have to drop the SQL Schedule and create a new one. It may not be what we wanted to do as it may take some additional time. Therefore, are we curious to know how to create SQL Jobs based on the same template but having a different SQL Schedule ID?. This post shows how to do it.

First of all, look at this picture.



You will see a parameter @schedule_uid which is the SQL Job schedule ID, so what we have to do now is to comment this line in order to allow SQL Server to generate a new ID for the SQL Job Schedule.



Having modified that parameter for each Job, the rest of Jobs will not inherent the Schedule ID anymore and a new one will be created instead. I hope this post is useful for you and let me know any questions. Until next post, thanks for reading!
HELLO, I'M PERCY REYES! — a book lover, healthy lifestyle lover... I've been working as a senior SQL Server Database Administrator (DBA) for over 20 years; I'm a three-time awarded Microsoft Data Platform MVP. I'm currently doing a PhD in Computer Science (cryptography) at Loughborough University, England — working on cryptographic Boolean functions, algorithmic cryptanalysis, number theory, and other algebraic aspects of cryptography. READ MORE