How to take data from the corrupted table.

Hi,
I'm using SQL Server 2000. One of the table in my database is
corrupted due to some resons. (I don't know the reson)
When i try to select all rows(Select * from tblProduct) from the
table then its giving the following error.
Server: Msg 823, Level 24, State 2, Line 1
I/O error (torn page) detected during read at offset 0x000000024c0000
in file 'D:\Data\SQL\Data\OTRC_Data.MDF'.
Connection Broken
The table is having around 100,000 records. But when i run the query
its executing the query batch and selecting Rows upto 69,810. After
this row its generating the torn page error. Now i want to take backup
from this table execpt the corrupted rows. Or else how can i solve this
problem? If anybody know the solution please let me know it.
Thanks and Regards,
Vinothkumar B
bvinoth@.tvsinfotech.com
None
1) Take a look at ERROR.LOG file to see what happened
2) Run DBCC CHECKDB which has an option to repair the data
3) If it does not help, restore the database with a different name and move
the data to the old database
)"None" <vinkumrect@.gmail.com> wrote in message
news:1165812584.993063.250690@.16g2000cwy.googlegro ups.com...
> Hi,
> I'm using SQL Server 2000. One of the table in my database is
> corrupted due to some resons. (I don't know the reson)
> When i try to select all rows(Select * from tblProduct) from the
> table then its giving the following error.
>
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (torn page) detected during read at offset 0x000000024c0000
> in file 'D:\Data\SQL\Data\OTRC_Data.MDF'.
> Connection Broken
>
> The table is having around 100,000 records. But when i run the query
> its executing the query batch and selecting Rows upto 69,810. After
> this row its generating the torn page error. Now i want to take backup
> from this table execpt the corrupted rows. Or else how can i solve this
> problem? If anybody know the solution please let me know it.
>
> Thanks and Regards,
> Vinothkumar B
> bvinoth@.tvsinfotech.com
>
|||Try running dbcc checktable on that table with Repair options after taking a
database backup. If it fails again u can try the below steps:
1. Create a new table with the same structure as the corrupted table
2. Write a script to copy the data from old table to new table record by
record (using Primary key)
3. The insert will fail once it reach the corrupted record; try insert from
next record
3. Rename the corrupted table to table_corrupt
4. Rename the new table to original name
5. Create all indexes for this new table
Note:
As part of your routine maintenance please schedule a DBCC CHECKDB for all
production databases. This will help you to identify issues earlier.
Thanks
Hari
"None" <vinkumrect@.gmail.com> wrote in message
news:1165812584.993063.250690@.16g2000cwy.googlegro ups.com...
> Hi,
> I'm using SQL Server 2000. One of the table in my database is
> corrupted due to some resons. (I don't know the reson)
> When i try to select all rows(Select * from tblProduct) from the
> table then its giving the following error.
>
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (torn page) detected during read at offset 0x000000024c0000
> in file 'D:\Data\SQL\Data\OTRC_Data.MDF'.
> Connection Broken
>
> The table is having around 100,000 records. But when i run the query
> its executing the query batch and selecting Rows upto 69,810. After
> this row its generating the torn page error. Now i want to take backup
> from this table execpt the corrupted rows. Or else how can i solve this
> problem? If anybody know the solution please let me know it.
>
> Thanks and Regards,
> Vinothkumar B
> bvinoth@.tvsinfotech.com
>
|||On Mon, 11 Dec 2006 14:22:56 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

> I
>have used optimizer hints in conjunctions with ORDER BY clauses to force an index so it reads the
>table "backwards". This way I can get all rows except perhaps those on one page (the corrupted
>page).
Very nice, Tibor!
Roy
|||None wrote:
> Hi,
> I'm using SQL Server 2000. One of the table in my database is
> corrupted due to some resons. (I don't know the reson)
> When i try to select all rows(Select * from tblProduct) from the
> table then its giving the following error.
>
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (torn page) detected during read at offset 0x000000024c0000
> in file 'D:\Data\SQL\Data\OTRC_Data.MDF'.
> Connection Broken
>
> The table is having around 100,000 records. But when i run the query
> its executing the query batch and selecting Rows upto 69,810. After
> this row its generating the torn page error. Now i want to take backup
> from this table execpt the corrupted rows. Or else how can i solve this
> problem? If anybody know the solution please let me know it.
>
> Thanks and Regards,
> Vinothkumar B
> bvinoth@.tvsinfotech.com
I recall dealing with a torn page on 7.0. At that time I set ROWCOUNT
to the highest value that did not raise an error. For exmaple
SET ROWCOUNT 10000
SELECT * FROM TableWithTornPage ORDER BY TableID
-- succeeds
SET ROWCOUNT 10001
SELECT * FROM TableWithTornPage ORDER BY TableID
-- fails complaining about torn page
So I ran
SET ROWCOUNT 10000
SELECT * FROM TableWithTornPage ORDER BY TableID
and got
Then I used a similar approach and got the rows from the end of the
table. There was only one torn page. Not sure if it is still relevant
to later versions.
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/