I am getting consistency errors while running DBCC CHECKDB
on a table that has corrupted rows on SQL Server 2014. I am running into corruption errors after the first 30,000 rows (total 1.4 million (14 lakh) rows).
I want to try to bcp
the data out until I get to the corruption. I want to skip the corrupted rows. I want to bcp
after the corruption range and minimize the data loss.
How can I do it? Please explain in detail.
See also my previous question: Consistency Errors running CHECKDB on a couple of tables in SQL Server 2014
Best Answer
First, here's my checklist on how to tackle corruption issues. It's a big deal - bigger than I can cover here - but based on the sounds of this, I don't think you've covered some of the basics (like trying to restore from backup, or making sure you've even got backups.)
When you start getting corruption errors, every time you access storage, you may be making the problem worse. For example, I dealt with one broken storage array where the more we read data, the more it became corrupted. (I'd read one table and it was fine, and then fifteen minutes later, it was hosed too.) Just like a haunted house, you need to get off that storage device as quickly as possible, and tackle your recovery efforts somewhere else.
Once you're on another storage device, if you can isolate the corruption down to a single table (which isn't likely, but you're hinting that you've done that), then:
Think about a phone book, for example, organized by last name, then first name. If you know that the page containing Ozar, Brent is corrupted, then you may have to select users up to my last name, but stopping before that - and then run a second query for users with a last name higher than mine.
Here's the trick, though: corruption is going to strike all over the table. You're going to be doing a ton of work trying to get around different corrupt pages.
It's not easy, and that's why I wrote my checklist - and why it doesn't try to tackle this portion. You're talking about days, if not weeks, of work when the corruption is widespread.