Database disasters are relatively few and far between, but when they occur, they can be devastating. Kalen Delaney discusses how you can prevent the loss of data and productivity after a disaster in "Disaster Prevention: Preparing for the Worst," page 24. But in this article, I talk about recovering from isolated disasters—specifically, failures that affect only part of your database. For example, maybe you lost a subset of data such as a table or part of a table. Although restoring the full database is always an option for recovering data (and sometimes your only option), certain backup strategies and third-party products might help you handle this type of disaster more efficiently and effectively.
Before discussing those strategies, however, let's look at what might cause an isolated disaster. One possible source is hardware failure. But a more likely cause of data loss and, ultimately, downtime is human error. Perhaps a DBA accidentally dropped a table or mistakenly deleted or updated rows because of an incorrect WHERE clause. Or maybe a developer didn't properly test an application's code, so users were able to access and manipulate the wrong data. Recovering from isolated corruption caused by human error can be more difficult than recovering from a hardware failure because you might not know exactly what was damaged, when the damage occurred, or the cause or extent of the damage. When you don't know that information, your recovery can be cumbersome, time-consuming, and prone to additional errors and further data loss. The cause of the disaster determines how you recover from it, so let's look at the ways you approach recovery after hardware failure and after failure caused by human error.
Recovering After Hardware Failure
As soon as your system sustains an isolated hardware failure, such as a disk failure, your database automatically becomes inaccessible and marked suspect. When a media failure occurs, the damage is easier to repair because you have only one recovery option: find another place suitable for the failed section of the database to reside. This spot can be a new permanent location or a temporary location until hardware is replaced or installed. Note that recovery after a media failure is much faster when you keep additional hard disks on hand or set aside supplementary disk space to which you can immediately restore the data by using the WITH MOVE command. Otherwise, you might have to order and wait for the replacement disks. After deciding on a location to which you can restore your data, you need to determine how you can best recover the data and to what point in time you can recover it. Your choices depend on your backup strategy, your recovery model, and your knowledge about what has failed.
When you lose only a data file, you might be able to recover the data up to the point of the failure with no data loss. This up-to-the-minute recovery is possible because SQL Server stores within the transaction log detailed information about the data that has changed. To achieve up-to-the-minute recovery, you must be able to access this detailed information in the transaction log of the damaged database and perform a transaction log backup. However, up-to-the-minute recovery might not be possible, and data might be lost in certain scenarios such as the following:
- You've lost the transaction log file or a part of it.
- You're using the Simple recovery model in the damaged database, and you perform only periodic full database backups.
- You're using the Bulk_Logged recovery model in the damaged database, and you've performed a bulk operation since your last transaction log backup.
In these situations, the tail of the transaction log (i.e., the transaction log of the changes up to the point of corruption) won't be available to be backed up, so you can recover data only up to the last backup. As a result, you need to manually rerun or reperform all the changes that occurred after the backup.
You can minimize data loss by running frequent transaction log backups (e.g., every 5 minutes). You can also impose restrictions on the amount of time a database can remain in a bulk-logged state by implementing the changes through batch processes, which programmatically change the recovery models. However, you should take time to determine your best log backup plan as well as your recovery model. (You can read my Web-exclusive article "The Best Place for Bulk_Logged," http://www.sqlmag.com, InstantDoc ID 39782, for details about some of the problems related specifically to the Bulk_Logged recovery model.)
Prev. page
[1]
2
3
4
5
6
next page 
