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?

Cheers,
Tony.

Keep up to date with Simple-Talk

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

This post has been viewed 5276 times – thanks for reading.

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

Tony Davis

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

Related articles

Also in Blogs

Ten Years Later

It’s hard to believe, but Simple Talk has now been going for over ten years. Thanks to brilliant pieces from our writers, hard work from the team here, and countless valuable contributions from you, our readers, we’re currently receiving one million page views a month, and sitting on a hefty 2,500 articles from over 370 … Read more

Join Simple Talk

Join over 200,000 Microsoft professionals, and get full, free access to technical articles, our twice-monthly Simple Talk newsletter, and free SQL tools.

Sign up