How to Develop a Good Database Backup and Recovery Strategy

 
 
By Brent Ozar  |  Posted 2010-01-14 Print this article Print
 
 
 
 
 
 
 

Database backup and recovery is becoming more difficult and complicated, especially with today's explosion of data growth and the increasing need for data security. Here, Knowledge Center contributor Brent Ozar outlines seven actions database administrators can perform to build a sound database backup and recovery strategy.

A backup and recovery strategy should address both data growth and security issues. It should provide a similar level of service for all platforms, enabling a single database administrator to control both backup and recovery operations and the backup files themselves. Finally, the backup and recovery strategy should maintain data security and minimize storage needs, backup times and recovery times.

Here are seven steps to consider as you build a sound database backup and recovery strategy:

1. Never back up databases to local disk

We back up SQL Servers so we can restore data in the event of a server crash. If the SQL Server crashes, especially if there is a hardware or severe operating system failure, you are going to need to be able to restore as fast as possible and the local drives may not be available. It's much safer to back up to a network share, which will allow you to begin restoring your backups to another server immediately and get back online faster.

2. After you back up the databases to a file share, back up the share to tape

Tape drives these days are fast enough that vendors like to say DBAs should go straight to tape. Technically, they're right: tape backup and restore speed is not a bottleneck. However, a limited number of drives are available and, when the DBA needs a restore right away, the tape drives aren't always just sitting idle. Disk backups, on the other hand, are always available.

3. Justify the cost of the network share by lower licensing costs and simpler backups

The storage area network (SAN) and backup administrators will need to have cost justification for a new dedicated array just for SQL backups. Here it is: the dedicated array will pay for itself by eliminating the need for backup agents on each SQL Server. It will allow them to have just one backup policy: back up everything on that network share once a day and get it off-site ASAP. They won't have to worry about peak loads or which servers are on which schedules; they'll only need to back up that one network share.



 
 
 
 
Brent Ozar is Quest Software's SQL Server domain expert and a Microsoft SQL Server MVP. Brent has a decade of broad IT experience that includes systems administration, project management and database administration. In his current role, Brent specializes in database performance tuning, SANs and data warehousing. Previously, Brent spent six years at UniFocus, a hospitality metrics company. Brent conducts training sessions, has written several technical articles, and blogs at www.brentozar.com. He is a regular speaker at PASS events, editor-in-chief of SQLServerPedia.com, and co-author of the book, "Professional SQL Server 2008 Internals and Troubleshooting." He can be reached at brent.ozar@quest.com.
 
 
 
 
 
 
 

Submit a Comment

Loading Comments...
 
Manage your Newsletters: Login   Register My Newsletters























 
 
 
 
 
 
 
 
 
 
 
Rocket Fuel