Using Third-Party Tools
You can also use third-party tools to investigate a corrupted database. Some of these tools offer options that let you reverse-engineer the transaction log and regenerate the T-SQL statements that performed the operations. These tools often can tell you which user performed which operation at what time. You can then use the reverse-engineered scripts to "change history," using the regenerated scripts as your form of recovery instead of restoring the transaction logs to the database. However, using these scripts could compromise data integrity—especially when you change history. If you remove some commands from the script but continue to run commands that followed the now-removed statements, the statements that follow might generate different results and different data. Additionally, other users might have based their work on information that will be reversed by these modified scripts. These tools can be invaluable, however, for finding and helping resolve the problem quickly and for possibly providing you with information that shows how you can avoid this isolated corruption in the future. For example, you might see an application vulnerability that you can close after the problem is solved.
However, the biggest advantage of these tools is that you can use them to read transaction logs after the changes have been committed. In contrast, SQL Server doesn't provide a mechanism for reading the transaction logs. When you use SQL Server tools, you can implement some kind of realtime auditing to determine who caused a problem and when the problem occurred, but you can use only triggers, a custom application, or tools such as SQL Server Profiler. With SQL Server's recovery capabilities, you must already know something about what was damaged and what to look for, but with third-party tools, you can go straight to the transaction log—which is more efficient.
The two primary third-party tools for investigating the transaction log are Lumigent Technologies' Log Explorer (http://www.lumigent.com), which Ron Talmage reviewed in "Log Explorer 3.03," January 2003, InstantDoc ID 27272; and BMC Software's SQL-BackTrack, which you can read about at http://www.bmc.com/products/proddocview/0,2832,19052_19429_23365_1058,00.html. Numerous links on the SQL Server Magazine Web site take you to product reviews, recommendations, and gotchas for many different products. You can download and test the free versions of available tools to decide whether they meet your requirements.
Evaluating Your Recovery
After you've recovered the data damaged in an isolated corruption, make sure you take stock of the event. Is there a place where security or application design was violated? In addition, ask yourself whether the recovery went as planned. What went wrong? What went well? What can you do to streamline the next recovery? And most importantly, evaluate what steps you can take in the future to prevent the same sort of event from happening.
Implement strategies that will prevent hardware failure in the future, and consider adding some form of hardware redundancy to minimize problems that might occur. And although you want to prevent human error as much as possible, recognize that no system is truly safe from every accident or mistake. By knowing your options for recovery, however, you can minimize downtime and return the system to a usable and stable state. Develop a few disaster-recovery plans that fit your network, test and document them, then update them as data, recovery models, or other related factors change.