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
<http://acornnetsec.com/>



.

Relevant Pages

  • Re: Corrupt form...or so it seems
    ... Tracy, 2 things come to mind from your description. ... Is it possible that the query that the ... > query which pulls from the now corrupt customer table. ...
    (microsoft.public.access.forms)
  • Re: Corrupt form...or so it seems
    ... > <The first is you stated that the form is based on a query. ... > the form that I think is corrupt is the Customers Table. ... it just happens to be where the first error message appears ...
    (microsoft.public.access.forms)
  • Re: Corrupt form...or so it seems
    ... <The first is you stated that the form is based on a query. ... the form that I think is corrupt is the Customers Table. ... Already set to break (an error message could very well be popping up ...
    (microsoft.public.access.forms)
  • Re: Query doesnt always sort
    ... to be happening with multiple databases on a regular basis, ... see this as a corrupt database issue. ... Try using DISTINCTROW in the query. ... reports more or less ignore any sorting done by the query. ...
    (microsoft.public.access.queries)
  • Recovering records from corrupted MSSQL tables
    ... I'm working with an MS SQL Server database residing on a disk that ... distill all remaining good records from a corrupt table, ... croak("No columns found in specified source table $stable!" ...
    (perl.dbi.users)