Data corruption and recovery issues
All the troubleshooting concepts discussed in this chapter relate to one common goal: protection and recovery of your most important asset, your data!
What is the key to recovering your data? The answer is simple. Restore from a valid backup. But you bought this book to find out "tips and tricks" for data recovery, and all I have told you is to restore from backup. Is that all? Well, not just that, but I am telling you that restoring from your backup is absolutely the most reliable and consistent method to recover your data. Why? BACKUP/RESTORE is SQL Server's primary mechanism for recovering your data in the most reliable and consistent fashion.
I have supported customers for every SQL Server version Microsoft has shipped over a 13-year period and have seen many customers contact technical support without the ability to restore a valid backup. SQL Server has such excellent tools to back up your data (and many ISVs have built products using our VDI API) that there really is no reason not to have a valid backup. It simply takes a well-thought-out strategy and the right hardware to ensure you have a backup to meet your recovery needs. The point I make here is that I will present many advanced features and techniques to recover your data; but in some cases, these would not be needed if you have the proper backups. But what if, despite your best efforts, you cannot restore from a backup? You've come to the right place. Although in some situations I discuss restoring from a backup as the best (and perhaps only) solution to a problem, I present other options specifically designed into SQL Server 2005.
Now let's take a look at how this chapter is organized so you can decide how best to read through this material. I have organized the chapter into three main sections that discuss building your knowledge, data recovery troubleshooting scenarios, and exercises.
If you want to build your knowledge in the area of data recovery, focus on this first section. I fill in some gaps from the product documentation on specific storage internal topics such as new allocation structure terminology. Second, I make sure you are educated on important SQL Server 2005 enhancements in the areas of backup/restore and DBCC CHECKDB. Third, I provide some tips and suggestions for best practices to avoid data recovery problems focusing on backup/restore, DBCC CHECKDB, and the system that supports SQL Server, the operating system, and the hardware. I said I wouldn't talk much about disaster recovery strategies, but I can't help it. One of my jobs at Microsoft is to educate and think of ways for customers to avoid calling technical support, which means thinking of ways to prevent problems. So I spend some time on best practices so that you can avoid using advanced techniques to recover your data.
The next section is all about troubleshooting, the main reason you purchased this book. Troubleshooting is all about solving problems. Problems can usually be categorized into various scenarios. Therefore, this section is organized into various scenarios that you might encounter, including failures to access, backup, restore, or check consistency on your data. If you want to learn about how to solve problems for specific scenarios, you should read this section. But as with all good books, many types of great technical tips and internal information are woven into this chapter. To teach you about troubleshooting data recovery, I first go over scenarios that require you to recover system databases. I then review how to troubleshoot situations when your user database is inaccessible (for example, your user database is marked SUSPECT). As mentioned previously, BACKUP/RESTORE is critical to data recovery. But what if it fails? Well, we talk about how to handle some of these situations. The last two subsections focus on database consistency. First, I review certain types of database consistency runtime errors. These are errors that can occur during execution of the most basic T-SQL queries (such as SELECT, INSERT, UPDATE, or DELETE) after the database has been successfully opened. Some of these scenarios may require you to use DBCC CHECKDB. So, I teach you what to do when DBCC CHECKDB reports errors. This is one of the most important tools in your data recovery toolkit, so it is important to understand more about how it works, proper usage, and what to do when it reports errors.
Use the following table of contents to navigate to chapter excerpts, or click here to view Data corruption and recovery issues in its entirety.
Book and author box:
| About the book: |
|
| In this book, bestselling author and SQL Server guru Ken Henderson has worked with the SQL Server Development team to write "the" troubleshooting SQL Server 2005 book. All of the content comes directly from the creators and developers of SQL Server. Ken has edited each chapter to ensure that the writing is clear and sound. Written by the people who know the product best - the people who built it - SQL Server 2005 Practical Troubleshooting teaches developers and dbas how to troubleshoot and diagnose problems they may encounter with SQL Server. Purchase the book from Addison-Wesley Publishing |
| ABOUT THE AUTHOR: |
|
| Ken Henderson has been a developer for more than 25 years. He has worked with SQL Server since 1990 and has built software for a number of firms throughout his career, including H&R Block, the Central Intelligence Agency, the U.S. Navy, the U.S. Air Force, Borland International, JP Morgan, and various others. He joined Microsoft in 2001 and is currently a developer in the Manageability Platform group within the SQL Server development team. He is the creator of SQL Server 2005's SQLDiag facility and spends his days working on SQL Server management tools and related technologies. He is the author of eight books on a variety of computing topics, including the popular Guru's Guide series of SQL Server books available from Addison-Wesley. He lives with his family in the Dallas area and may be reached via email at khen@khen.com. |
Home: Introduction
