Export data excluding torn pages?

  • I have a table that has numerous torn pages as a result of a previous corruption of the database. Running dbcc checktable returns about 1400 errors in a 6000 record table. I have tried the various repair options but none have helped.

    Is there any way of exporting the data from the table excluding the torn pages? When I try to dts the entire table it hangs when it hits the first torn page. Select * from table only returns the first 114 rows.

    I know I could dts each row individually but this would take a long time as when I reach a row with a torn page it takes 4 mins to cancel the query!

    Any advice would be appreciated.

  • If you're really motivated, and you don't have a backup, this will be a long and tedious process. You need to analyze the full set of error messages that DBCC gives you to figure out which pages in the table are readable, and then do a DBCC PAGE on each of them to get the starting and ending key values on the page, and then do targeted SELECTs to hit each page.

    Unless the data is really critical, I wouldn't attempt it myself just because of the time involved.

    Do you have a backup? How long has the data been corrupt before you discovered it? How often do you run CHECKDB?

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply