June 23, 2009 at 11:07 am
Hi,
We have 5 sql server 2005 instances in one server and we taking the backups of all the 5 instances to only one Backup drive. Here:
If we schedule the backup of all databases in all 5 instances at the same time, there will be more Disk IO. So to aviod this, is there any script to check
. the backup of the databases in instance 1 has been completed or not, if completed then only go and backup databases in second instance.
and then after completion of second instance backups goto 3rd instance and then 4 and then 5.
If the 1st instance backups are not completed, there should be some wait time and check the backup status again. Is this possible in sql server?
June 23, 2009 at 11:18 am
By looking at the BAckups history, you should have a fair idea of how much time each instance takes.
then schedule each instance backups at diff times.
Else you willl end up with more complex scenarios... or if something hanges then everything is laid back... sometime, when we are eager on improving things, we should take a simpler approach or we end up improving the disaster scenarios... as i used to do 😀
Maninder
www.dbanation.com
June 23, 2009 at 1:04 pm
You could purchase a license to SQLSentry, which will help you handle this and prevent them colliding with each other. Otherwise, I like Mani's solution. Just look at history, schedule them near each other, about when one completes. It won't hurt anything if they are running at the same time, though they might be slow.
June 23, 2009 at 1:18 pm
Just a little alternative to the above replies.
Maybe you can just create the backups on a local drive, in the last step of your local backup job, robocopy the .bak files to the designated folder(s) of the shared drive.
This way, it will only be the copy operation that will take longer.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 23, 2009 at 4:01 pm
Thank you..
one more question..Do you take the Tape backups of Differential and log backup files too? Is that necessary.
Because currently we are taking the Tape Backups after the full backup is finished. Lets say if Full Backup is finished at 2:00 PM, we are Taking Tape Backup of those .Bak files at 2:30 PM.
Is the above strategy is fine or do we need to Take the Log backups (I mean after the backup is done, copying the .trn files to Tapes) to Tape?
June 24, 2009 at 12:51 am
Mani (6/23/2009)
Thank you..one more question..Do you take the Tape backups of Differential and log backup files too? Is that necessary.
Because currently we are taking the Tape Backups after the full backup is finished. Lets say if Full Backup is finished at 2:00 PM, we are Taking Tape Backup of those .Bak files at 2:30 PM.
Is the above strategy is fine or do we need to Take the Log backups (I mean after the backup is done, copying the .trn files to Tapes) to Tape?
We create local backups (full and log ).
We don't use differental backups that much (only if the log load is very high and we would actually benefit restore time in case of recovery)
And yes, we copy these backup files to a shared location, which is begin copied to tape.
(The backup jobs (log incremental and fullbackup) each copy to a different location to avoid overwriting log backup files and breaking the sequences)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 26, 2009 at 3:37 am
Hi,
Is there a reason you can't get the first backup job to call the second and so on? That way the second would only always start after the first has completed but with no delay. 🙂
June 26, 2009 at 4:45 am
MO (6/26/2009)
Hi,Is there a reason you can't get the first backup job to call the second and so on? That way the second would only always start after the first has completed but with no delay. 🙂
I think this can be done too with one sql server agent doing job of all other 4 agents in ur case.
Edit- huh internet issue,.
June 26, 2009 at 8:37 am
You could link the servers together and have one Agent call sp_start_job on the other server.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply