SQL Server: Advanced Corruption Recovery Techniques

Database corruption happens all the time, all over the world, and sometimes it's not so simple to deal with. In this follow-on course from SQL Server: Detecting and Correcting Database Corruption, you'll learn the more advanced techniques that can lead to success in the most challenging corruption scenarios, applicable for more experienced SQL Server professionals, from SQL Server 2005 onward.
Course info
Rating
(120)
Level
Advanced
Updated
Nov 22, 2013
Duration
3h 57m
Table of contents
Introduction
DBCC CHECKDB Internals and Performance
Useful Undocumented DBCC Commands
Dealing With Transaction Log Problems
Advanced Restore Techniques
Advanced Repair Techniques
Description
Course info
Rating
(120)
Level
Advanced
Updated
Nov 22, 2013
Duration
3h 57m
Description

Database corruption happens all the time, all over the world, and sometimes it's not so simple to deal with. The SQL Server: Detecting and Correcting Database Corruption course showed you what you need to know to detect and recover from most cases of database corruption and this course will take you to the next level of knowledge and capabilities. The course starts by explaining in depth how DBCC CHECKDB processes databases and how you can tune the performance of DBCC CHECKDB to make it run as fast as possible. You'll then learn about some undocumented DBCC commands, such as DBCC PAGE and DBCC WRITEPAGE, which can be invaluable when investigating and repairing corruption. The course then moves on to dealing with transaction log corruption, such as using EMERGENCY mode and how to re-attach damaged databases, and more advanced restore techniques, including how to analyze the transaction log to find the point to restore to and how to perform a tail-of-the-log backup using a different SQL Server instance. Finally, the course ends with advanced repair techniques, including how to deal with corrupt metadata by manually editing system tables, how to salvage data from backups and nonclustered indexes, and the ultimate in advanced recovery techniques: manually editing a database using DBCC WRITEPAGE. Packed with a wealth of information and practical, easy-to-follow demonstrations, this course will prepare you for dealing with out-of-the-ordinary corruption problems. The course is applicable to all SQL Server versions from SQL Server 2005 onward, and for more experienced SQL Server professionals.

About the author
About the author

Paul is the CEO of SQLskills.com, a Microsoft Regional Director and a SQL Server MVP. He worked on the SQL Server Storage Engine team at Microsoft from 1999 to 2007. Among other things, he wrote DBCC CHECKDB/repair for SQL Server 2005 and was responsible for the Core Storage Engine during SQL Server 2008 development.

More from the author
More courses by Paul Randal
Section Introduction Transcripts
Section Introduction Transcripts

Introduction
Hi. This is Paul Randal with SQLskill. com, and I'm recording this course for Pluralsight. This is the SQL Server: Advanced Corruption Recovery Techniques course, and you're watching Module 1, the Introduction. Why is this course important? Well, as I said in the basic course corruption does happen many, many times a day all the way around the world, and you can prove it to yourself by just looking at some of the various corruption forums that are out there on sites like sqlservercentral. com. Many people out there don't realize that they have corruption because they're not checking for corruption by enabling page checksums or running regular consistency checks, and then they've got corruption, and it starts to spread. And these same people, they don't know what to do when they have corruption. We covered all of that kind of more basic stuff in the SQL Server: Detecting and Correcting Database Corruption course. What we're going to cover in this course are some of the more advanced topics and techniques, some of the kinds of things you don't see every day, things that you might find are actually outside your comfort zone, you've never tried these things before, and some of the real last resorts like going in and manually fixing things using DBCC WritePage or even just a hex editor. The information in this course is really going to take you to the next level in terms of being able to recover from corruptions.

DBCC CHECKDB Internals and Performance
Hi. This is Paul Randal from SQLskills. com, and I'm recording this course for Pluralsight. This is the SQL Server: Advanced Corruption Recovery Techniques course, and you're watching Module 2: DBCC CHECKDB Internals and Performance. Introduction. In the basic course I talked a bit about DBCC CHECKDB and the various other check commands, and I gave a little bit of information about what they do, but in this course I want to dive a little bit deeper into the internals of CHECKDB and talk about some of the performance enhancements that you can make to make your consistency checks run a lot faster, very important if you're doing any kind of disaster recovery. So, in this module we're going to cover some of the internals of CHECKDB, some of the trace flags that you can use to make CHECKDB perform faster, and also some of the things you can do around parallelism, constraining parallelism, and constraining memory settings that are going to make CHECKDB run faster as well, kind of non-intuitive things that you need a little bit more advanced knowledge to be able to figure out.

Useful Undocumented DBCC Commands
Hi. This is Paul Randal from SQLskills. com, and I'm recording this course for Pluralsight. This is the SQL Server: Advanced Corruption Recovery Techniques course, and you're watching Module 3: Useful Undocumented DBCC Commands. Introduction. Apart from the 30 or so DBCC commands that are documented in Books Online, there are well over 100 undocumented DBCC commands as well. Most of the undocumented DBCC commands are only actually useful for the test teams in the SQL Server Product Group at Microsoft, but some of them are really, really valuable when we're dealing with database corruption and we want to look at database structures, pages, and so on. In this module we're going to talk about DBCC IND, DBCC PAGE, DBCC DBINFO, DBCC DBTABLE, and DBCC WRITEPAGE.

Dealing With Transaction Log Problems
Hi. This is Paul Randal from SQLskills. com, and I'm recording this course for Pluralsight. This is the SQL Server: Advanced Corruption Recovery Techniques course, and you're watching Module 4: Dealing with Transaction Log Problems. Introduction. One of the modules in the more basic class talks about the causes of database corruption, and the most common cause of database corruption is the I/O subsystem going wrong in some way. Now, transaction logs are files just like data files, and so I/O subsystem caused corruption can hit a transaction log just like it can hit data files. There are a variety of different things that could be wrong with transaction logs, and so the way you recover from a damaged transaction log depends on a few things like the state of the database and whether it's attached or not. In this module then we're going to talk about transaction log problems, and we're going to cover how you can recover from a damaged or missing transaction log, how to use the EMERGENCY mode to access a database where recovery couldn't run properly, how to use EMERGENCY-mode repair, and how to reattach a detached database that's in the SUSPECT mode and fails a regular attach.

Advanced Restore Techniques
Hi. This is Paul Randal from SQLskills. com, and I'm recording this course for Pluralsight. This is the SQL Server: Advanced Corruption Recovery Techniques course, and you're watching Module 5: Advanced Restore Techniques. Introduction. I'm sure that most of you out there that are watching this course have done simple things using database restores restoring a database to a particular point in time using WITH NORECOVERY, maybe using WITH STANDBY, but there are a bunch of extra things you can do, more advanced options to restore particular parts of a database, to be able to restore to a particular transaction point, and also restoring system databases. Now, before we get into these, remember that to be able to restore you have to actually have backups, and those backups need to be valid to avoid having data loss. So, you have to make sure that you have a good backup strategy, and you also have to make sure you have a good backup testing strategy, things that I talked about in the restore module in the more basic course. In this module we're going to cover doing tail-of-the-log backups when the original server is not available using a hack-attach method, doing page restores, partial restores, and piecemeal restores, examining log and log backup contents and being able to restore to a particular log sequence number, and restoring system databases.

Advanced Repair Techniques
Hi. This is Paul Randal from SQLskills. com, and I'm recording this course for Pluralsight. This is the SQL Server: Advanced Corruption Recovery Techniques course, and this is Module 6: Advanced Repair Techniques. Introduction. The most advanced techniques that you're going to use for affecting any kind of disaster recovery involve manually editing databases, either manually editing the system tables themselves or manually changing page structures themselves using DBCC WRITEPAGE. All of these techniques that I'm going to show you are very challenging, but as a last ditch effort it could end up saving your business or your job. So, in this module we're going to cover system table corruption and what you can do about it, circumstances when you definitely don't want to run repair because it's likely to make things worse, how you can reconstruct deleted data using nonclustered indexes, and manually editing data files.