A solid backup strategy can save your VLDB
In the event of a disaster, fast and effective recovery of your database environment is essential. You must be able to quickly implement your disaster-recovery plan—which must be tested and well documented before the disaster. A successful disaster-recovery plan lets you recover your database within your company's defined acceptable amount of downtime and data loss, if any. Because backup and restore are important and required parts of any disaster-recovery plan, your backup/restore strategy should minimize both data loss and downtime. And when your database is measured in hundreds of gigabytes—or even terabytes—the plan to minimize downtime and restore quickly becomes even harder to achieve as database size affects recovery times and backup complexities.
Creating, testing, and maintaining a database environment where little to no data is lost and downtime is entirely avoided in a disaster is no trivial task. More important, in your quest for high availability, remember that more than the database can fail. Many SQL Server features—such as failover clustering, log shipping, and replication—offer high-availability solutions. However, regardless of the options you choose and the levels of redundancy your hardware provides, you'll always need a solid backup strategy. No matter what size the database or the availability requirements, restore is always an option—and in some cases, such as accidental data modifications or deletions, the only option—that lets you restore the database to a state before the modification.
Whether you're recovering from accidental data deletion, hardware failure, natural disaster, or other unplanned incident, your backup strategy is the foundation of a solid recovery plan. You can restore backups to different servers or different file locations, and you can easily send them to geographically dispersed locations either electronically or on removable media. Backups offer the most options for the lowest cost and require little additional hardware, except maybe backup storage devices such as tape. But you still need to take time to fully understand all the features and potential pitfalls to ensure that your backup is as automated as possible and your restore is as fast as possible. (For more information about restoring databases, see Kalen Delaney's articles "All About Restore," May 2002, InstantDoc ID 24340, and "Safe Transit," page 33.) With SQL Server 2000, backup and restore options are easy to automate and combine to create a flexible, effective recovery path. All backup/restore features are included with all SQL Server 2000 editions and don't require Enterprise Edition. Moreover, by practicing some of the advanced options I discuss here, you can minimize downtime and reduce data loss even in the event of total server failure.
What's Your Backup Strategy?
On many production servers, the backup and restore strategy is periodic complete database backups (e.g., weekly) with frequent log backups (e.g., hourly); some shops add occasional differential backups (e.g., nightly except on the day when full backups occur). The basic strategy is acceptable, but adding differentials helps minimize downtime by reducing the number of logs you have to apply to roll forward the database; you need to apply only the full backup, the most recent differential backup, and the logs since the last differential backup. However, both strategies must use a full backup as the starting point for a restore. For details about SQL Server 2000's backup types, see Michael Reilly's Certifiably SQL column "Backup Strategies," September 2000, InstantDoc ID 9629.
If you have a very large database (VLDB)—measured in hundreds of gigabytes or even terabytes—you need to ask yourself a few questions about your backup strategy. How long does your VLDB take to back up? More importantly, how long does it take to restore? Are these times acceptable based on your business requirements? Unless your strategy uses storage-assisted backup such as split-mirror, a restore that uses one of the full-backupbased strategies is likely to take hours. Even if hours of downtime are acceptable, how much data can you afford to lose? And what's your site redundancy strategy—do you use log shipping or do you copy all your log backups to a secondary site?
SQL Server pauses log backups while a full database backup is running. The Web-exclusive sidebar "Log Backups Paused for Good Reason," InstantDoc ID 26032 at http:\\www.sqlmag.com, explains the technical reason for this requirement, but you could lose a lot of data if you had a site failure during a full backup. This scenario might sound unlikely, but when your full database backup is measured in hours, your risk of data loss increases. If your database backup takes 8 hours to perform, your secondary site could be as much as 8 hours behind at the time of a failure, resulting in 8 hours of data loss if the backup wasn't complete or hadn't copied to the secondary location. Losing 8 hours of data is unacceptable in most cases. But what if a log backup could occur while you were performing a different backup? Such an approach would let you ship changes to another site even while your large backups are occurring. To let logs back up every minute (a common frequency for minimizing data loss), you might choose not to perform a full database backup at all. By using a file and filegroup backup strategy, you can completely avoid performing a full database backup, so log backups will never be paused. This strategy lets a secondary site's data stay as close as possible to the primary site's and minimizes potential data loss.
Prev. page
[1]
2
3
next page 
