SQL Server Backup and Recovery Software

Dozens of SQL Server backup and recovery solutions exist, ranging from SQL Server's own native backup functionality to offerings sporting rich feature-sets (and hefty price tags). I've collected tips and advice from SQL Server backup and recovery experts to help you decide when you might need to upgrade from SQL Server's native backup capability to a more robust third-party solution, and what features to look for when you decide to upgrade. To compare several backup and recovery products, see the table.

Back(up) to the Basics

When you begin looking for a SQL Server backup and recovery solution, your first stop should be with SQL Server itself. If you're dealing with a relatively small database, SQL Server 2008/2005's native backup and restore features might be sufficient. "I typically tell customers that SQL Server's native backup functionality works very well, and is a very viable solution in most environments," says SQL Server Magazine contributing editor (and SQLServerAudits.com consultant) Michael K. Campbell.

Although native backup and restore might fit the bill for some database projects, Brent Ozar—Quest Software's SQL Server domain expert—points out the deficiencies of SQL Server 2005 native backups in a post on his personal blog. Ozar explains that native SQL Server 2005 backups lack data compression, so backup files tend to be massive. Writing backup files can be terminally slow, and Ozar points out that "SQL Management Studio doesn't come with reports about the backup process." SQL Server 2008 introduces data compression and encryption into the native backup process, but SQL Server DBAs with massive databases to manage might want to look at more comprehensive backup offerings.

When to Upgrade to a Backup and Recovery Product

How do you know it's time to upgrade to a more comprehensive backup solution? Campbell says he considers a number of use-cases that would lead him to recommend third-party solutions to clients. "\[If\] they're running low on disk because \[they're\] keeping 2 to 3 days worth—or more—of backups, which takes up too much premium disk space, or if their data is sensitive enough that it should be encrypted while at rest." Other situations in which Campbell recommends the use of a more robust backup solution include if you need to use your network to keep offsite copies of backups and your databases are very large or you could otherwise benefit from a third-party product's 40 to 60 percent compression. Finally, Campbell also leans towards third-party solutions if you’re dealing with extremely large databases that need to be backed up on a regular basis: "If an organization is regularly restoring 300GB to 800GB (or larger) databases, then compression of those backups results in significantly less I/O, which can help boost the speed of the backup, especially \[when the admin has\] done a good job of using multiple file groups."

Features to Look For

What features are important in a SQL Server backup and recovery product? "\[I would look for\] the ability to do online backup, the ability to restore a table or file group, and the ability to back up full text catalogs," says Michael Otey, technical director for SQL Server Magazine. "I'd also look for the ability to back up FileStream objects, \[as well as data\] compression, encryption, and a T-SQL command-line interface."

Campbell has reservations when it comes to choosing a solution that requires installing too much software into an IT environment. "I'm personally a bit leery of the kinds of software third-party vendors deploy on machines in order to enable new features and functionality," explains Campbell. "Ideally, configuring a target server for third-party backup should just consist of dropping a couple new special stored procedures in the master database, and registering a couple of .dlls on the box—all of which should be handled very easily and painlessly from the command console that ships with the backup solution."

The Importance of Backup Scheduling

"Remember that backups that aren't regularly checked for viability are potentially worthless," says Campbell. "I always tell my clients: 'We've all worked in companies where a server or something critical has crashed, and the folks in IT looked like complete idiots because their backups weren't working correctly.' Likewise, I always mention that CEOs and other executives usually don't know a thing about backup software, other than that it's supposed to protect them if something goes wrong, so failure to regularly tests backups and work through simulated recoveries is a capital offense. In other words, the worst time to learn how to recover a database—either from native SQL Server backups or with third-party software involved—is after the sales database has crashed and while 300 people are waiting for it to be recovered before the business can become profitable again."