SQL Server Backup and Recovery Software

The June issue of SQL Server Magazine is out – featuring a Buyer's Guide with "tips from SQL Server Backup and Recovery Experts" designed to help you figure out when to use a third party backup application, and what to look for if you decide to upgrade.

Is SQL Server's Native Backup Functionality Viable in MOST environments?
Of course, I'm sure I'll potentially catch some flak for saying that SQL Server's native backup capabilities are "a very viable solution in most environments", but I stand by that assessment. Of course, "most" is a very subjective word – and it's definitely not the same as "all". Mostly what I meant by it though, is that SQL Server comes with backup capabilities that work fine out of the box – and which should work in well, most, environments where there are smaller databases and less stringent disaster recovery requirements. And given that there are sooo many SQL Server deployments out there with only a handful of smaller SQL Server databases on a given server, my "most" statement makes sense. As such, for many businesses, the price of deploying SQL Server doesn't AUTOMATICALLY require the overhead of a third party backup solution.

And, as my series of Video Tutorials on SQL Server Backups showcases, it's pretty easy to get solid disaster recovery solutions in place, and while SQL Server Management Studio doesn't provide the best reporting on backups, there are some rudimentary details that should work fine in most environments where there are less than 20-30GBs of databases in production.

When to Turn to Third Party Backup Solutions
On the other hand, there are definite cases where you'll want to look into using Third Party Backup Solutions. As the Buyer's Guide points out, I recommend 3rd Party solutions to my clients when they're schlepping larger (or more heavily used) databases over the wire – where compression becomes a BIG factor, where databases are larger (say above 20-30GB), where recovery times need to be as fast as possible, and/or where data at rest needs to be encrypted.

SQL Server 2008 introduced native backup compression, but, frankly, I'm not that impressed by it. First of all, it costs $20k more per processor. That may sound a bit silly, but it's effectively true. Because either you're buying Enterprise Edition because you already need the features it offers, or you're buying Standard Edition (at $5k/socket instead of $25k/socket) because you don't need the extra BI, online, reporting, and other capabilities of Enterprise Edition. But no one in their right mind would/should swing another $20k/socket to get 'free' backup compression. Moreover, in my estimation, most environments using Enterprise Edition are already going to have compression/encryption solutions in place, so SQL Server 2008's native compression sounds great on paper, but it's practically useless in my book (and other DBAs feel the same way as well).

So, if you've got larger databases, need to encrypt your data at rest, or just need FASTER backups (and ensuing recovery), then you'll NEED a third-party backup solution. And don't worry about the extra cost – it will pay for itself the first time it saves your bacon.

Third Party Solutions
SQL Server provides a set of native APIs that vendors can use to address the specific issues of improved backup/recovery performance, storage, and security. So, if you're looking for SQL Server Backup solutions, make sure you're going with a solution that implements these NATIVE APIs. In other words, steer clear of so-called 'SQL Server Backup Solutions' that just use Shadow-Copy to grab a copy of your files out from underneath SQL Server. These solutions WON'T give you the kind of coverage you really need, and I've personally witnessed the havoc they can cause when they take 'ignorant' snapshots of SQL Server data and log files that don't account for how SQL Server works in terms of logging and recovery and therefore leave databases 'backups' in 100% non-usable form.

Then, as I mentioned in some of the quotes from the Buyer's Guide, third party backup solutions SHOULDN't require you to install a LOT of software on the machines/SQL Server's that you'll be managing. Yes, whereever you end up installing the management console or primary management node for your third party solution will typically require a full-blown software installation; and if you're only managing a single server, then that's fine. But what I'm talking about here (and in the Buyer's Guide) is that you should be able to VERY easily deploy new backup 'agents' or 3rd-Party capabilities to OTHER managed servers with just the addition of a couple of .DLLs and some new added special stored procedures. You shouldn't have to install lots of bloat on each managed server – that should only be necessary on your primary or central management/backup server.

My Personal Preferences
Otherwise, I've personally only really used Red Gate's SQL Backup and Quest's LiteSpeed with enough day-to-day exposure to be able to provide solid recommendations for or against. (I've obviously played around with a few other backup solutions – but not enough to really get a feel for how they perform day to day or pan out over time.)

Happily though, both SQL Backup and LiteSpeed are phenomenal products.

Red Gate's SQLBackup
SQLBackup is definitely (in my mind) more geared at smaller shops where there is only a single server or just a handful of servers to be managed – and it's pricing ($795/server or $295 for the Lite Edition (which doesn't provide encryption)) typically reflects that very well. It can, however, manage multiple servers though without much difficulty. As a consultant, I've recommended SQL Backup to a number of my clients who need faster, smaller, and/or encrypted backups – and it's been a very viable solution that works dependably and reliably day in and day out. It's also very easy to use and configure – which is a big bonus for smaller shops where there may not be as much dedicated talent on hand to master all the ins and outs of SQL Server (i.e. companies where the DBA has been 'upgraded' to their position from either an in-house developer or other IT professional who is constantly playing a game of catch-up between their normal responsibilities and their DBA-related tasks).

Quest's LiteSpeed for SQL Server
Likewise, I'm equally as impressed with LiteSpeed. It's a great product that offers excellent compression, reporting, and encryption capabilities. It also offers MUCH greater granularity in terms of what kinds of objects can be restored (i.e. you can restore individual tables and objects ONLY instead of entire databases – which is very cool). In my mind though, it's much more enterprise-caliber in the sense that while it's viable for single servers (or just a handful of servers), it REALLY starts to shine when we're talking about managing backups on LARGE numbers of servers. And, as you'd expect, the pricing for LiteSpeed really reflects this as well.

In fact, if I had any complaint at all about LiteSpeed it's that I _really_ hate how Quest does business: by making you call for a quote, rather than just posting prices online and offering volume discounts like other more 'modern' vendors. That said, given that Quest is doing a great job of hiring forward-thinking, no-nonsense, DBA evangelists and experts like Brent Ozar and Kevin Kline, as well as dumping substantial energy into facilitating great resources like SQLServerPedia, I'm hopeful that some-day the winds of change will prevail at Quest and we'll see a more open, and more hassle-free, pricing scheme. Otherwise, I have nothing but praise for the solution itself.

In Conclusion
The key thing to remember though, is that we can talk backup solutions until we're blue in the face. But unless you've got a documented and well-rehearsed disaster recovery scenario in place, it won't matter how much you're spending (or not spending) on backup solutions if your backups are no good. And as I intimated in the Buyer's Guide:

• Managers/Bosses/CEOs don't care about backup solutions or details. They only expect them to work. 
• Your backups aren't working unless you regularly VERIFY that they're working by testing them out.
• The WORST time to learn that your backups haven't been working is during a disaster where you need your backups.
• The WORST time to learn how to RECOVER a database is during a disaster.

So, get a solution in place, test it thoroughly, document it accordingly, and optimize/update your disaster recovery documentation whenever you make changes.

Otherwise, if you're looking for more background on backups and best practices, make sure to check out SSV's current series of free video tutorials on SQL Server Backups. There are currently over 2.5 hours of FREE content covering everything from backup basics to log file management, SQL Server backup best practices, and how-to tasks showing you exactly what you need to do to backup and restore your SQL Server databases. And once you've mastered those basics and principles, using 3rd Party Backup Solutions will only BUILD upon the skills you've already mastered.