Backup Solution for Moderate Usage SQL 2005 Server

Question

Instead of sticking with our normal Backup Exec & LTO Tape system that we've used for production servers in the past, I'm looking at alternatives for our new SQL 2005 server that will go live in about 2 months.

What kind of software and hardware are you guys using to backup a single SQL server? This server will see a moderate amount of usage during normal business hours and little-to-no usage outside of business hours. I expect to have no more than 500GB of data that needs to be backed up on a regular basis. In the past, we've done full SQL and system backups overnight to a tape library, alternating sets of tapes weekly. I'd like to move away from storing 8-10 tapes per week to maybe a removable storage solution like the Dell RD1000. Does anyone have any real-world experience using a RD1000 for nightly backups? Similarly, is Backup Exec still a decent choice or should I be looking elsewhere?

Thank you!

Answer 1

That heavily depends on your demands. If it is fine to lose a whole day, you could stick to full backups every night. Otherwise you may wanna do full backups every night and log shipping in short periods. With this configuration, you can restore the last full backup and roll forward to a given point in time using the saved transaction logs.

What type of medium you choose is another question. Tapes are IMHO not necessary these days. HDDs and offshore servers are so cheap these days.

Answer 2

Not really a tape or removable solution but we use transaction log shipping every 10 mins to a warm standby server, and when we did have a catastrophic failure one day, just pointed the connection string to the 2nd server and kept on trucking.

Answer 3

For close to a year now, I have been using Quest LiteSpeed for most production backups, instead of Native SQL backups, or direct-to-tape agents like Backup Exec. For the savings of the backup footprint alone, I have found it to be well worth the licensing cost. The next thing it saved me is time, for both backups and restores. On beefy hardware, compression does not pose too much of a burden, and backing up 200GB is faster than backing up 500Gb, no matter how you do it (YMMV, but my LS backups are consistently a quarter of the native backup size, or less). You can still use backup Exec too, but you won't need the SQL Agent, because you are just backing up the backup files. Even with system backups, if you go from 10 tapes a week to 5, you are saving a lot of money in the long run.

The restore piece is great because of the brick-level options. Having to restore a TB database to fix 5 records is a thing of the past, you can restore single objects or queries on objects to the destination of your choice. Time, money, sometimes bits of sanity are saved there.

It has been years since I've used the Backup Exec agent, and it has possible they have added features like this, but from their site, it doesn't look like it. There are also similar solutions besides LiteSpeed, but I haven't used them, so I can't comment on their relative worth.

Answer 4

We have been deploying Red Gate SQL Backup for some servers of that size. It seems to be significantly faster than Backup Exec for disk based backups and restores. It's also fairly reasonably priced.

Answer 5

First, be sure you use either the native SQL Server backups or SQL Backup/Litespeed/Hyperbac to back up to DISK. While I like tape for getting things offsite, it's not reliable, nor quick enough for backups from SQL Server. BackupExec , Arcserver and other agent based solutions have never been reliable enough for me. I would use them to move files to/from disk to tape, but not from a process like SQL Server. They just have failed enough times in the past that I won't trust them.

A nightly backup process works fine, however if someone whacks a table or you get something corrupted, chances are that you will want to recover to the last hour, or minute. So I'd be sure that you have at least hourly transaction log backups.

I don't typically keep more than 2 days of backups around, but that depends on how far back you might need to go and regulatory issues. The maintenance plans in SQL Server 2005 can help you set these up as well as remove old backups. Note, create a new backup before deleting old ones.

I know lots of people want to just use disk, and I can understand that IF you can get your data offsite overnight. That requires bandwidth, and you know if you can do that. If you can't get it offsite on a wire, do it with tape.

Iron Mountain, or some similar service, likely exists in your city to daily pick up tapes and rotate them offsite. You could have them pick up USB drives just as well, but make sure things get offsite regularly.