Batch Job Creation
warnerra
Posts: 5
Hello!
I have just purchased SQL Backup 4.6 and I must create 3 jobs each for 200 databases (weekly FULL, nightly DIFF and daytime incrmntal LOGs every 2 hours).
Each database goes to a different existing directory, not named for the database. Each database requires encryption using a different password, and each database requires notification to a different email address.
I also intend to stagger the schedules over 2 minute intervals based for weekly full, nightly differential, and daytime T-Log backups every two hours in order to minimize impact on performance of the server.
I wish to maintain a single name for each backup output file so that it is replaced by the next execution of the job, but I also want to copy the backup to a sub-directory upon completion for safekeeping until the next job finishes successfully.
What options, if any, are available to automate the process of creating these jobs given these requirements, even if the automation is partial?
Thanks,
Rick
I have just purchased SQL Backup 4.6 and I must create 3 jobs each for 200 databases (weekly FULL, nightly DIFF and daytime incrmntal LOGs every 2 hours).
Each database goes to a different existing directory, not named for the database. Each database requires encryption using a different password, and each database requires notification to a different email address.
I also intend to stagger the schedules over 2 minute intervals based for weekly full, nightly differential, and daytime T-Log backups every two hours in order to minimize impact on performance of the server.
I wish to maintain a single name for each backup output file so that it is replaced by the next execution of the job, but I also want to copy the backup to a sub-directory upon completion for safekeeping until the next job finishes successfully.
What options, if any, are available to automate the process of creating these jobs given these requirements, even if the automation is partial?
Thanks,
Rick
Comments
This could take quite a bit of scripting. If there is a requirement for different passwords on each database, that means having a separate backup job for every database. You could get SQL backup to overwrite existing files using the INIT command in your backup script so you could back up the database into the same file every time.
If the backup fails, though, it may be a better idea to use ERASEFILES to clean up any outdated backups so the previous backup will only be deleted after the next backup is successful.
Thanks for the reply.
Yes, I understand there is no easy way to do this as I have played with different approaches. I see pros and cons no matter which way I go. Having even a simple requirement to update the notification email address can have large implications if I make it a single job with multiple calls to the stored procedure. I have a template worked out, so it's not too tedious.
I have one additional question. Because I am making the backup file available for download via https to my customers, I must contend with the file extension type or else convert the file to a type that is acceptable, such as a zip type. Does any approach lend itself for a final step to zip the backup COPYTO file?
Thanks again,
Rick