July 18, 2013 at 5:24 am
In SQL 2008 R2 (sp2) our vendor requires that thier 14 SQL database FULL backups execute in a sequential order. (to preserve data integrity in the event of a restore)
EG. DB_1 gets backed up, when finished, DB_2 gets backed up, etc. (thes 14 FULL Backups cannot run in parallel)
I'm familiar with starting a job via: EXEC msdb.dbo.sp_start_job @ScheduledJobName --- but thinking, if I submit 14 of these EXEC msdb.dbo.sp_start_job statements -- they'll initiate asynchronously and run in paralllel.
Anyone have a script or suggestion which will run jobs, serially, waiting for the previous job to complete? thx
July 18, 2013 at 5:36 am
I'd say if you have 14 separate jobs, they should not be scheduled to execute at all. i'd actually remove them to avoid confusion.
instead,
shouldn't you create one single job with 14 steps, and each step calls the CREATE BACKUP command with the proper parameters in the correct order?
steps of a job don't execute in parallel, right?
Lowell
July 18, 2013 at 5:37 am
You could accomplish this by adding a second jobstep to each backup-job. In this second jobstep you start the next job. Only the first job has a schedule attached.
So:
You or a schedule starts job 1 with step 1 (backup).
When step 1 completes it continues with step 2 (start next job).
Job 2 is started with step 1 (backup).
When step 1 completes it continues with step 2 (start next job).
Job 3 is started with step 1 (backup).
When step 1 completes it continues with step 2 (start next job).
... etc.
It's quite some administration, so you need to keep track of all changes in databases, jobs, etc. You can choose if step 2 of the job is executed if step 1 succeeds and/or fails.
July 18, 2013 at 6:48 am
I completely agree with the 14 step approach Lowell suggested. I've used this approach myself to address a similar situation and it has worked very well. Each step runs sequentially. You also won't have 14 individual backup jobs.
July 18, 2013 at 7:02 am
I prefer to have one job per database, in case I want to back up databases individually, but that's by-the-by.
If I were you, I'd make some enquiries about the vendor's requirement. I can't see how backing up sequentially preserves data integrity. If the way the backups are performed is so important, ask to see the recovery plan. If you have to do it that way, then you have the following choices:
(1) Put all the backups in one job
(2) Have each job call the next when it finishes
(3) Put all databases in a single maintenance plan (native or third-party)
(4) Find a tool that allows jobs to call each other synchronously (I don't know of any).
John
July 18, 2013 at 7:13 am
If you had one job that looked at a table to pick the list of databases to backup, you could use this to manage the fact that you only wanted specific databases to be backed up rather than all. I've done it that way in the past. Use this table also to control the backup type, full, differential, log etc...
July 21, 2013 at 11:33 am
Download the backup solution from ola.hallengren.com and you can schedule a single call to his stored procedure to backup all databases sequentially in one job step.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply