Secondary Backup / Restore Strategy

Question

Secondary Backup / Restore Strategy

Phil Auer

Mr or Mrs. 500

Points: 543
More actions
November 12, 2008 at 7:08 am

#201329

I have a production database approximately 100 GB in size.
My primary backup software is Hewlett-Packard's Data Protector with the SQL Server extension. It calls SQL Server APIs to backup up directly to a tape library. I have scheduled a full backup twice a day and transaction log backups every hour in between.
I am a big believer in having a 'second path' back to the data in case the first backup fails to restore due to a tape, tape library, etc. malfunction.
I currently do a standard full database backup to disk at 2:00 AM every morning. If all else fails, I can basically get back to the previous day's data. Due to disk space constraints, I can only do one of these per day.
I am looking at third party backup solutions specifically to get backup file compression. I'm pretty sure I can't run concurrent transaction log backup schemes because the inactive entries are truncated when logs are backed up but I think I can do a secondary full / differential scheme which will keep me much more current in case I have to go to restore plan B.
With a tool providing 90-95% compression, I think I can schedule secondary full backups at least twice a day and differential backups every couple of hours in between, with retention of a couple of backup sets. If it works, it would guarantee that I would lose at most a couple of hours in the case of a catastrophe. Fortunately, in our environment, we have ample CPU and I/O capacity to handle the backups without affecting production activity.
I'm not a SQL guru and would like some opinions as to whether this makes any sense at all.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you can get a USB Terabyte and a half external drive for as little as 180 bux; whether you zip/rar an backup, or use a 3rd party tool, tha tmight be a cheap and vialbe secondary backup option.

something that simple saved one of my servers when a raid drive went down; Dell came out and said the controller had been writing bad data for most of the day, and I was able to recover quickly because of a secondary path of backups that i do (and restore frequently)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

Phil Auer (11/12/2008)
I am looking at third party backup solutions specifically to get backup file compression. I'm pretty sure I can't run concurrent transaction log backup schemes because the inactive entries are truncated when logs are backed up but I think I can do a secondary full / differential scheme which will keep me much more current in case I have to go to restore plan B.
With a tool providing 90-95% compression, I think I can schedule secondary full backups at least twice a day and differential backups every couple of hours in between, with retention of a couple of backup sets. If it works, it would guarantee that I would lose at most a couple of hours in the case of a catastrophe. Fortunately, in our environment, we have ample CPU and I/O capacity to handle the backups without affecting production activity.

The external drive is a good idea. That's what we are doing until our new data center is built.

90-95% compression is a bit optimistic. More like 80 %, and if you have image data like we do, then even lower.

I don't see the point of the multiple full & diff backups. Take 1 full, then frequent trans logs (every 15 minutes or less). Copy them to multiple locations if you're worried about them. Then you won't lose hours of work, just minutes, or possibly zero if you can get the tail log backed up.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 3

I like Diff backups if you can do them because they speed and simplify restores. I have run them every 2-6 hours at times to ensure that I don't have 80 logs to restore when something dies 23 hours after my last full backup.

Definitely put the extra disk backup on a separate drive. The last thing you want is that your array is down with data and backups on it.

The direct to tape thing, I'd scrap the SQL APIs. I have almost never seen this work when needed. I haven't used the HP product, but the other 2-3 I have don't work. Or they haven't and I am not willing to bet my job or reputation on them. Just run your backups to disk, let the HP product just grab the files like any other files.