0

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.

Improve this question
2
  • 1
    If you just want to have your backups in two places (ie two different disk systems, one onsite & one offsite, etc), consider using MIRROR TO. For instance, BACKUP DATABASE YourDB TO DISK = 'C:\YourDB.bak' MIRROR TO DISK = '\\SomeServer\SomeFolder\YourDB.bak'
    – DMason
    Commented Mar 17, 2014 at 16:51
  • My issue with doing something like that is that I was trying to use the existing maintenance plans left by the old DBA. They were fully created by the wizard so it doesn't look like I can add that anywhere. I'm going to go ahead and use David Crowells suggestion of downloading a script and modifying it so that it writes to two different places. Thanks!
    – Jamie T
    Commented Mar 17, 2014 at 20:20

4 Answers 4

Reset to default
4

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.

Improve this answer
1
  • 1
    Thanks David, that's exactly the kind of response I was looking for. I currently am writing them both to a network location and then they're being backed up to tape. I just wanted to know if my method of creating the backups was the wrong way to go about it (which it was). Thanks everyone.
    – Jamie T
    Commented Mar 17, 2014 at 20:05
0

If your network is fast enough using the MIRROR option is doable but make sure it does not affect your backup window. I have used third party products that would mirror to another location and often times when the job failed, it was because of the mirror location having a read error during the verification process. The backup wrote quicker to the network share than it did reading it which ended up causing the job to fail all the time. It can cause a few headaches so just be aware.

If you want to make sure your data is as safe as possible you should include testing your backup strategy to make sure it works like you think it will, and not just one time. This will also give you a good idea of restore times to meet any SLAs that might be in place.

Improve this answer
0

Another point worth adding:

If you ever take a differential backup, it will use whichever full backup was most recent as its base, meaning you would potentially have to determine which system to retrieve the full backup from in order to restore a differential (and hope that said system is still operational). You can use the COPY ONLY option to take a full backup without resetting the differential change map, but you can't use that copy-only backup to restore any differentials.

It's best to have one official sequence of full/differential/log backups being taken for recovery purposes, and use COPY ONLY for doing things like restoring test copies of data.

Improve this answer
0

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.

You actually do not need two backup sets in two different places. To make sure that you can restore your database to a point-in-time, you have to test - test and test your RESTORE strategy.

By duplicating your backups without testing your RESTORE strategy, to me its just waste of resources esp Disk (even though you may disagree by saying that disk space is cheap now :-))

Imagine a scenario where you have all the process inplace for taking backups - full and log backups. And someone took an ad-hoc T-Log backup. This will break your Log chain and you wont be able to restore your database to a point-in-time - UNLESS you have tested your restore strategy (you wont know that it does not work).

Refer to : Backups: Planning a Recovery Strategy by Paul Randal.

Also, depending on your database size and your business requirements, you should workout if you want

  • daily/weekly - Differential / FULL backup

  • Transaction Log backup - Per minute, Hourly, etc

Best is to use Ola's script (as David mentioned) as the Backup solution is customizable as per your need.

Improve this answer
1
  • Thanks for the link to the Recovery Strategy Kin. While you're probably right about not requiring the data in two places, one of the servers we're backing up to is kind of old and being replaced soon, so it helps me sleep a little better at night. As soon as I have the backups sorted out I'm going to be setting up a recovery strategy just like the one you linked. Thanks!
    – Jamie T
    Commented Mar 19, 2014 at 13:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.