SQL Environment Backup Question

Question

DBAs out there,

We have a SQL environment that needs a backup solution upgrade, and we are looking for some input from the community. There are 8DBs spread across 4 servers total in 100GB of size, plus a 9th DB that is 450GB that is excluded in the numbers below. All are located at a data center with a large amount of bandwidth (OC-3).

Currently backups are run from SQL Management Studio Maintenance Plans to an array at the data center with the DBs. Off site backups are conducted by an employee plugging in a USB drive weekly and x-copying over the data and taking that drive back to the office. We would ideally like to remove the aspect of a human having to come into the data center to pull the data off.

Currently the backups are daily fulls. Lite-speed is available for compression and we could continue to run daily fulls for comfort's sake, or we could move to a mixture of 1 full 3 days diff plus trans for some of the DBs and 1 full 6 days diff plus trans for the other DBs, instead of all fulls. Any thoughts on that?

One off-site option is use DFS, or another copying mechanism to sync the data center backup share to a share down at the office/off site location over a T1 at night. In running the numbers if we were to move to diff plan, we are looking at ~6GB most nights, and ~19GB on Saturday nights of backup data. If the full T1 was available to the syncing process that would be 9 hours on most nights, and 27 for Saturday.

Another option is to purchase a device with an off site backup subscription to send data from the off-site location to the 3rd party data center, but we could be looking at a steep cost.

It's about 59.5GBs of data a week, so if we wanted to keep 2 months that's roughly .5TB.

Any thoughts?

Thanks in advance!

Answer 1

I think it all depends on how much money you can spend and what type of retention you need. Our infrastructure group has a SAN with TB's of storage available to us. We have a network share to a folder on the SAN that we write all of our SQL backups to. We have over 15 SQL servers and 150+ databases. I have 1 maintenance plan per database for full backup (daily, off hours) and 1 plan for trans logs (every 15 minutes). Both plans delete files older than 24 hours to keep the backup location cleaned up. This allows me access to a days worth of data in case of any issues. The backups are then backed up to tape nightly as well. Those can be kept for as long as we need. Ours is currently 3 months.

I definitely agree that you need to remove the human aspect as you said. Ideally you shouldn't really have to touch any of it (except for maybe tapes).

I personally don't like the off site options since I can at least go put the tape in myself if I had to. I know you have agreements with them for service but still...

Hope that helps give you some ideas.

Answer 2

Would definitely go with differential backups and, if you can get away with it, once a month full backups that get hand carried if the backup window won't allow for that time to copy them over.

"KISS" Keep it simple.

Answer 3

For restoring the mixture of fulls/diffs/t-logs are a lot better choice. There will be less data loss, as at most with the t-log you lose an hour of data instead of an entire days worth. As I have read before, the reason why people do back-ups is for the restores.

Answer 4

Every week we do two full and 4 incrementals (Almost nothing happens on sunday in our system so thats a good time to run cleaning). 1 full is kept on-site and we have it setup to rotate the tapes (We have a very nice tape library that we have 23 tapes and 1 cleaning tape loaded into)

1 full is sent offsite. The first full of the month goes into a safety deposit box, the others go to a vendor, Iron Mountain who actually comes here and picks them up.

Totals on our backups is about 1.2Tb on the fulls and 140Gb on the incrementals. Using differentials helps us a lot because the fulls take nearly 22 hours and doing it every day was ridiculous. We use Backup Exec 11d with a HP MSL G3 Tape library

Answer 5

Fifth post -- I agree with most everything posted so far, and won't repeat it.

When to do Complete/Differential/TransactionLog backups depends on what your systems do and when. Often, this works well with the weekly cycle, as one day tends to be the off day (Sunday, or maybe Monday when you're cleaning up after the weekend business). I'd recommend Complete backups at least weekly; if that backup goes bad, you have to go back to the prior one, and losing up to two week's worth of data is not nearly as bad as losing up to two month's worth. (And there's always something... other dept. at a place I worked at once hit the "no valid complete DB backup for months" situation ['cause they weren't making them, but that's a different story]. After restoring several weeks of t-log backups, they hit one that was corrupt, and had to go with plan B: dig out the paperwork and re-enter several month's work of everything.)

If Sunday is a slow day, doing reindexes, weekly archives, and whatnot before the weekly complete backup is a good idea, so that all that busywork doesn't get loaded in a diff backup. (I'm comfortable with a weekly full backup, but as ever, much depends on your business and data usage patterns.)

It sure sounds like copying the data across the net to the home office isn't very practical. What happens when data grows beyond your ability to copy it over one night?

Tape backups are good and pretty automatable (though they do cost money). An offiste storage service is also good; they cost money, but since they have to visit you anyway, maybe you can get them to hit the server room? (Depends on security, access, etc. etc.)

Answer 6

Personally I prefer to do full backups on a daily basis and keep three generations on disk - I don't tend to use a full/differential mix because of the risk of the full backup not restoring successfully. I understand this is difficult if your databases are large - but 100GB isn't too massive these days.

Make sure you have the latest version of LiteSpeed and use the highest level of compression (unless users have access to the system at the same time your backups run).

Consider removing any redundant tables.

Check for and remove any unused indexes.

Remove fragmentation from your tables and indexes.

Consider archiving older data to another database and back this up less frequently.

Answer 7

It depends on your budget, but my company just moved from tape to a deduplicating SAN-based system with a mirror in a second data center - and I LOVE it. Completely rocks.

I have any small DBs set up with full compressed backups nightly, and larger ones with twice- or once-weekly fulls and nightly diffs, all to local disks to facilitate restores. Mission critical stuff adds transaction log backups and log shipping to a warm failover SQL server off site.

From the files on local disk, automated tape backup software (virtual tapes, not real ones) copies the backup files into one end of the dedupe mirror, and the mirror replicates to the other data center automatically. It's like magic, only better.