Recovering records from corrupted MSSQL tables

I'm working with an MS SQL Server database residing on a disk that
suffered a crash. There are a couple of very important tables in
there, and I'm trying to recover as much data as possible. One of the
tables in question holds ~2M records and, while I can still get
success with a 'select count(*)' query, any type of 'select *' query
chokes about 700K records in.

Here's my thinking on this: unlike all-or-nothing tools that error out
when they hit a corrupted record, I believe I can use DBI's
cursor-based, iterative methods (e.g. 'fetchrow_arrayref') to at least
distill all remaining good records from a corrupt table, skipping over
the corrupted "problem" records.

I have built a tool (using DBI, of course) that replicates the table's
structure to a second, intact database. It then uses the metadata
that it has already gleaned to formulate a correct 'insert' statement
(i.e. the right number of placeholders).

This works like a charm on good source tables, but I haven't even
tried it on a corrupted table yet because I can see that it doesn't
have a chance of working. I'm trying to get my head around how to
compose my code so that I can get my 'fetchrow_arrayref' into an
'eval{...}' block so that I can catch individual record errors and
record them (I need to know how many bad records there are), but still
keep working my way through the table to find subsequent good records.

Here's a snippet of what I have so far:

========================================
croak( "No columns found in specified source table $stable!" ) unless
$col_count >= 1;

my $placers = $col_count > 1 ? "?, " x ( $col_count - 1 ) . "?" :
"?";

my $walk_sth = $sdbh->prepare( "select * from $stable" );
$walk_sth->execute();

my $isrt_sth = $tdbh->prepare( "insert into $ttable values (
$placers )" );

while ( my $row_ref = $walk_sth->fetchrow_arrayref()) {
$isrt_sth->execute( @$row_ref );
}
========================================

Any sugggestions you might make about how I can deploy 'eval{...}'
blocks into this to make it able to slog through the entire table --
instead of stopping at the first corrupt record -- would certainly be
welcome.

Thank you,

-Brian

_________________________
Brian H. Oak CISSP CISA
Acorn Networks & Security

Re: Recovering records from corrupted MSSQL tables

It should be possible to reformulate the while-loop like this:

while (1) {
my $row_ref = $walk_sth->fetchrow_arrayref();
if (not defined $row_ref) {
if ($walk_sth->err()) {
warn "found invalid record\n";
# implicit 'next;'
} else { # no more records
last;
}
}
$isrt_sth->execute( @$row_ref )
}


Robert

----

Brian H. Oak schrieb:
> I'm working with an MS SQL Server database residing on a disk that
> suffered a crash. There are a couple of very important tables in
> there, and I'm trying to recover as much data as possible. One of the
> tables in question holds ~2M records and, while I can still get
> success with a 'select count(*)' query, any type of 'select *' query
> chokes about 700K records in.
>
> Here's my thinking on this: unlike all-or-nothing tools that error out
> when they hit a corrupted record, I believe I can use DBI's
> cursor-based, iterative methods (e.g. 'fetchrow_arrayref') to at least
> distill all remaining good records from a corrupt table, skipping over
> the corrupted "problem" records.
>
> I have built a tool (using DBI, of course) that replicates the table's
> structure to a second, intact database. It then uses the metadata
> that it has already gleaned to formulate a correct 'insert' statement
> (i.e. the right number of placeholders).
>
> This works like a charm on good source tables, but I haven't even
> tried it on a corrupted table yet because I can see that it doesn't
> have a chance of working. I'm trying to get my head around how to
> compose my code so that I can get my 'fetchrow_arrayref' into an
> 'eval{...}' block so that I can catch individual record errors and
> record them (I need to know how many bad records there are), but still
> keep working my way through the table to find subsequent good records.
>
> Here's a snippet of what I have so far:
>
> ========================================
> croak( "No columns found in specified source table $stable!" ) unless
> $col_count >= 1;
>
> my $placers = $col_count > 1 ? "?, " x ( $col_count - 1 ) . "?" :
> "?";
>
> my $walk_sth = $sdbh->prepare( "select * from $stable" );
> $walk_sth->execute();
>
> my $isrt_sth = $tdbh->prepare( "insert into $ttable values (
> $placers )" );
>
> while ( my $row_ref = $walk_sth->fetchrow_arrayref()) {
> $isrt_sth->execute( @$row_ref );
> }
> ========================================
>
> Any sugggestions you might make about how I can deploy 'eval{...}'
> blocks into this to make it able to slog through the entire table --
> instead of stopping at the first corrupt record -- would certainly be
> welcome.
>
> Thank you,
>
> -Brian
>
> _________________________
> Brian H. Oak CISSP CISA
> Acorn Networks & Security
>
>
>