17 January 2013

Getting backs up about backups

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?


For more articles like this, sign up to the fortnightly Simple-Talk newsletter.


  • Rate
    [Total: 0    Average: 0/5]

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.

View all articles by Tony Davis