In the last decade, Microsoft has brought SQL Server a long way – bringing in things like .NET code, data encryption, partitioning, and a darned slick management user interface. But one of the things that hasn’t changed has been the process of backing up and restoring data. We still back up to a file, and we still restore the entire database. The process is clunky, usually manually managed, and no new backup/restore features have arrived since Microsoft has been focusing on things like the BI stack. (I can’t exactly complain about that!)

Other vendors, though, have introduced several new features that give database administrators new tools in their backup & recovery arsenal. I’m going to talk about these features in general, not how a specific vendor implements them, because I want to cover so much ground, and some of the products are easier to show in a single screen shot. Here we go:

Object Level Recovery – restoring single objects and rows

The most urgent restore requests seem to be the smallest ones: someone dropped a single table, someone deleted a key record, someone hosed up a stored procedure. Everyone expects the DBA to be able to recover objects in a moment’s notice, but SQL Server’s native backups don’t make that easy. We have to restore the whole database, then pluck out the specific objects we want, and copy them over to the online database. What a hassle! Today’s third party backup software automates all of that by browsing inside the backup file, letting us pick specific objects, and asking us where to restore them. When testing this capability in backup software, look for these abilities:

• Restore multiple objects at once
• Select objects without reading the entire backup file (time-consuming)
• Script capabilities (so that you can create a single restore script, then reuse it for the exact objects you want without browsing through the entire backup file)

Central dashboard to manage all SQL Servers

A DBA that manages more than ten servers wants to spend less time checking their backups and restores. Vendors solved this problem with simple, intuitive dashboards that show whether any backups were missed in the last, say, 24 hours, or whether any database hasn’t been backed up in a certain time range. The DBA can see at a glance that all of the servers are protected. Contrast this with the native SQL Server backups, where there’s no graphical way to glance at a server and know that all of the databases are backed up. Some things to watch for:

• Manage servers without an agent – useful for non-licensed servers
• Central database of managed servers – so you don’t have to maintain the server list on every DBA’s workstation

Detailed progress status for currently running backups

I’ve taken this one for granted since I started using Idera SQLsafe and Quest LiteSpeed, and whenever I have to deal with a plain SQL Server, I can’t believe I lived without it. At a glance in the management UI of either program, I can see exactly how much has been backed up so far. Sounds crazy, but when the DBA is dealing with a 2 terabyte data warehouse, he doesn’t want to wait around to find out if data is getting out of the server. Look for:

• Status monitoring from anywhere – on any DBA’s workstation, they should be able to see status updates for any server’s running backups

Encrypted database backups

For public companies and medical companies, this just sells itself. Database administrators who don’t have backup compression software can get this budgeted just by selling it as the way to encrypt database backups.

I also wrote a tech brief for Quest called “10 Things DBAs Probably Don’t Know LiteSpeed Can Do.” It requires registration, but hey, it’s free. (I guess that means it’s not one of the best things in life, eh?)

Continue Reading Features missing from today’s SQL Server backup software.