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.comNone
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.googlegroups.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.googlegroups.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
>|||In addition to the other replies:
As you have noticed, SQL Server stops the read operation when it encounters
the corrupted data. 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 p
age (the corrupted
page). If above doesn't make sense to you, I suggest you hire someone who is
familiar with medium
level TSQL coding.
Also, here are my general recommendations for suspect or corrupt databases:
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"None" <vinkumrect@.gmail.com> wrote in message
news:1165812584.993063.250690@.16g2000cwy.googlegroups.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/|||> Very nice, Tibor!
Thanks, Roy. I wish the customer (for my first such job) agreed...
This was back in the 6.x days. I was not involved in the HW decision, maint
routines or anything, I
was only called in to do this emergency salvage. No DBCC CHECK... ever perfo
rmed, crappy hardware,
way old backup and a corrupted database.
I managed to so INSERT ... SELECT based on different index hints, and got al
l some 500 000 rows,
except one page, some 10 rows (which I did a hex dump of, for the developers
). And of course the
customer were very unsatisfied with losing that information... :-(
But that was a great learning experience. Make sure the customer agrees on e
xpectations before you
come close to do anything... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:n7uqn2pb6h2li9peh55k42s4onhvai9rq6@.
4ax.com...
> On Mon, 11 Dec 2006 14:22:56 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>
> Very nice, Tibor!
> Roy