How to Develop a Good 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.

Next: Do Regular Fire Drill Rebuilds and Restores >>