November 1, 2005 at 11:45 am
I want to stagger a backup of multiple databases so they aren't all kicking off at the same time. Is there a property or something I can look at? A status is sysprocesses perhaps?
November 1, 2005 at 11:55 am
Why not just run backups of your databases in series rather than in parallel.
BACKUP DATABASE db1 TO DISK=filename1
BACKUP DATABASE db2 TO DISK=filename2
etc...
Its a fairly easy TSQL script to create to backup your databases one after another.
You could also have different job steps for each backup and on success or failure you move to the next. This would help with error handling by the job history tables.
November 1, 2005 at 12:53 pm
I agree with Jason that you should run the backups as a train (one behind the other).
A way to determine that that backup is running is to look at the "syslockinfo" table.
If the database is being backed up you will see rows with rsc_text of [BULK-OP-LOG] and [BULK-OP-DB] (rsc_dbid is the database ID that is being backed up).
I was able to use this successfully in order to bypass an hourly log dump if the database was in the process of being backed up.
Rick
November 1, 2005 at 1:00 pm
Another way to tell is by looking at sp_who2 and look for something that says backing up.....
Good Hunting!
AJ Ahrens
webmaster@kritter.net
November 1, 2005 at 3:05 pm
The out of box maint plans do backups in a serial fashion for all selected dbs. Do you need a more robust backup plan? What are you using to do backups now. How are you kicking them all off at the same time?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply