Tony Davis is an Editor with Red Gate Software, based in Cambridge (UK), specializing in databases, and especially SQL Server. He edits articles and writes editorials for both the Simple-talk.com and SQLServerCentral.com websites and newsletters, with a combined audience of over 1.5 million subscribers. You can sample his short-form writing at either his Simple-Talk.com blog or his SQLServerCentral.com author page.
As the editor behind most of the SQL Server books published by Red Gate, he spends much of his time helping others express what they know about SQL Server. He is also the lead author of the book, SQL Server Transaction Log Management.
In his spare time, he enjoys running, football, contemporary fiction and real ale.

Getting backs up about backups

Published 17 January 2013 5:11 pm

I’ve been leafing with interest through the book, Pro Data Backup and Recovery, by Steve Nelson. For anyone predisposed to consider backup strategy largely from the perspective of a SQL Server database administrator, there are some revelatory passages, and a few that may cause you to splutter coffee over your keyboard.

According to one such passage, "Native tool backups should be avoided unless there is a very specific operational need…[they] provide no benefit over the use of the backup software tools". The term backup software tools refers to the likes of CommVault or NetBackup, which install agents to backup files, databases, mail and so on, and then various components schedule and execute all the backups and transport the data to the designated backup storage area.

It is interesting to get a system administrator’s eye view on the world of backups, where SQL is just one bit of the puzzle, and the DBAs’ insistence on pesky native backups is something of an inconvenience. The eye of the sys admin or engineer is on the need to ensure they recover all important business data within recovery time objectives, but also on planning and optimizing storage architecture, capacity and scheduling across the enterprise. In this picture, native/local backups represent a ‘loss of control’ over the backup environment as a whole. There are other issues too, for example with database backup compression and encryption defeating de-duplication technology in the enterprise backup tools.

The recovery of the databases and data in their care is just one of many reasons why DBAs need quick access to a backup, such as to do an object level restore, or a point in time restore to pinpoint a corruption problem, to do an audit trail to determine when a particular data change was made, and so on. DBAs for good reason need direct access to their backups, without intermediation, they need tight control over backup scheduling, for example to quickly reschedule if a backup fails, and they need the backups to run quickly and efficiently.

Enterprise-wide backup tools have traditionally had a poor reputation among many DBAs in all three respects, and their voice still holds strong sway in determining overall backup strategy. Is this changing? Steve Nelson paints a somewhat different picture of a backup world. He suggests that the performance of database backup via enterprise backup software has improved considerably. Using these tools and a combination of Virtual Device Interface (VDI)-based database backups, and VSS (Volume Shadow Service) snapshots allow the DBA to enable "recover the database to a particular point in time", and mitigate the need for native database backups.

Is he right? How many DBAs manage systems where native backups (or dedicated database backup tools) play little role? What is lost and gained as a result?

12 Responses to “Getting backs up about backups”

Our company’s hosted “Managed Backup Solution” was ditched for SQL Backups because it comprised full backups once a week, daily diff backups and NO LOG BACKUPS!

Knock me down with a feather – how crass is that?

“Proper” native backups ensure we’re out only 15 minutes max if we lose an ldf file, so point-in-time is a snap, always available and so far superior to what the host offers as to be on a different planet.

So much to lose with managed backups – so much gained with SQL Backup Pro. I certainly wouldn’t entrust my data to any kind of non-native solution.

I have several times the last years reviewed 3rd part backup solutions, and every time we decide to stick with SQL Server native backup. The issues are by example response time, single point of failure, possible data loss or service level.
I continue to listen to the 3rd part backup consultants, and still hope that some day they come up with something that will improve my SQL Server recovery plan.

Once you take your backups, you need to get the files “somewhere” offsite. That is where the third party tools become handy. Also, the compression functionality in the native backup has certainly helped to fend off the 3rd party tools.

Working together with the infrastructure group to ensure you get timely, accurate backups that are restorable is an issue most DBAs have had. It requires adequate planning, testing and communication. Too often, the sales team for the backup hardware is inadequately trained for SQL and SharePoint backup strategies. Sometimes their software is so ancient, they might as well relied on the native SQL Backup tool. Then when it comes time to restore, the DBA is more often on the line to get it done and done quickly. It is here that communication becomes very important so that everyone understands all the issues and timeliness of what is occurring.

There is something to this. Past a certain point in size, native tools just can’t cut it. SQL Server backups of a 5+tb database are too slow in most instances, so I can see the need for low level backup processes there. Anything less than that, I always say the same thing, “Don’t tell me how good the backup process is. Show me a restore, and a restore to a point in time.” Get that done efficiently and I’ll use your process. Most don’t work that well.

In the end it really depends on what data we are dealing with and what importance we grant to it.
A while back I wrote an article (http://www.simple-talk.com/sql/database-administration/handling-backups-for-rapid-resilience/) about backup resilience and it suggested that there is much more to backups than the data itself; furthermore it really depends on what and how we want to access first after a data failure.
I completely agree with Grant, though:
“Don’t tell me how good the backup process is. Show me a restore, and a restore to a point in time.”

I consider SQL Backup Pro a must on my critical SQL Servers now. Yes we run a server backup on top of our native SQL Server Backup, but for small backups you just can’t beat the simplicity and reliability of a native backup, especially when combined with a tool like SQL Backup Pro to add compression and encryption.

We are actually using DPM for our backups at the current time. It allows our sys admins to handle backups well for all of our servers. It also does more file-level diffs that can be restored at the higher level while still taking transaction log backups so you can do point in time restores. Sadly, we’ve only really tried to use the file-level restores and those can be painful because they’re not really native backups. You have to make space and attach the files.

My personal preference is still native backups or a SQL-specific tool (Red-Gate, Litespeed, etc). From there, you can back up the backups. I also like that there are sometimes nice add-ons like object-level recovery (something I do miss from the 6.x days).

As for backup processes, the restore is obviously the most important thing, but sometimes looking at the actual process is important as well. I’ve seen some processes that can bring the server to its knees while backups are running. I’ve seen some that don’t actually backup the files well. The worst was writing to the same disk as the database, but not handling a full disk well or even noting that the backups were failing. Restoring from the backup taken with a full disk was … interesting. (And unnecessary – as was learned after we tried to restore the bad backup.)

I remember 3rd party enterprise-y backup solutions that looked like they worked, but wouldn’t actually restore a database. Those were great until I asked for a restore and we spent days trying to restore a database. I insisted that we switch to native backups to disk at that point. The software would restore files, but wouldn’t restore SQL Server backups so we could back up our files and restore those if needed.

I could be persuaded to rely on generalized backup software tools, but only after extremely thorough testing. And even then, I would be reluctant. There are some areas where redundancy of options is a very good thing, and in my opinion backups are one of them.

Without a compelling reason to forgo native backups and very thorough testing to show me that I could safely do so, I would want native backups (or one from tested SQL-specific software like Red Gate’s SQL Backup).

For one thing, there is a value in being conservative and using tried and tested solutions for something that can be mission critical such as backups. For another, backups can be used for purposes beyond providing a backup, such as refreshing a test or development environment.

I also have little sympathy for the lack of deduplication caused by encrypting the backups. If backups are not encrypted in some way then they make a convenient target for getting at a large amount of sensitive data. I have an article in final editing/review stages about reasons and methods for encrypting backups currently.