I did my best to search for an answer to this question and couldn't find a scenario quite like this.
I'm a fairly new DBA in the sense that I haven't had a ton of training, I basically took the job over when a co-worker left.
This question pertains to our server that's running SQL Server 2005.
Recently I've been trying to make sure that our data is as safe as possible and thus want to have two backup sets in two different places. I currently have one set of plans doing a full backup, system DB backup and hourly transaction log backups to one server and then another set of plans doing the same thing but storing them on another server. The transaction log portions of the plans are also staggered by 30 minutes so that in the event I need to restore I can choose the most recent.
Is this the proper way to go about doing this? I've read that backup striping is usually done via a backup script and it sounds like the maintenance plan in 2005 isn't capable of doing this. Is that the case? Can anyone say if my method is usable or could this create problems in the future?
Thanks for your help.
Best Answer
You probably don't want to put your backups in two places like that alternating. You (probably) won't be able to do a point-in-time restore if one of your file store locations is lost. You'll be stuck with the latest full backup of the remaining system.
DMason's suggestion is workable, but personally I would use the scripts from http://ola.hallengren.com/ and back up to a network location. From there, you can backup up the files to tape or another disk.