Re: Restoring ONE table from a backup?
Cc:
In article <jaegerwl.805086022@wckn.dorm.clarkson.edu> jaegerwl@wckn.dorm.clarkson.edu (Bill Jaeger) writes:
>Howdy!
>
>I've got an Informix v5.0x database up and running with about 15 gigs of data.
>Of that 15 gigs of data, one table (perhaps 50K in size!) has become corrupt.
>I have complete, current level 0 backups that were done with tbtape.
>
>My problem is that I need to find a way to extract ONLY this one table from
>the level 0 backup set, and then insert that table into the database in lieu
>of the corrupt version.
>
>Since the Informix manuals are really lacking in this area I called Informix
>for guidance. The only thing tech support could suggest, other than upgrading
>to version 7 (great, thanks...) was to do a level 0 dump of the current
>database, restore the level 0 with the table I need, dbunload the needed
>table, nuke the database again, re-restore the current version, and then
>dbload the table.
>
>Is it just me, or does this seem ludicrous? Backups are great, but I'm not
>confident enough to trust the backups unnecessarily - moving 15 gigs of data
>back and forth to retrieve a 50K table just doesn't seem to make sense.
>
>Since I don't agree with Informix's advice, I went digging through the
>tapes with dd and found the data/table that I need to restore. Unfortunately
>this data is not in a directly restoreable format.
>
>So, what I'm asking is:
>
> - is it possible to somehow restore a single table from a tbtape dump?
> Using a shareware/PD/homegrown utility would be fine...
>
> - does anyone know the format of the data on the tape so that I could
> somehow piece it together?
>
>If anyone has any suggestions, please let me know!
>
>Thanks a bunch,
>-Bill :)
> bill@wckn.dorm.clarkson.edu
>
This is for an HP-UX computer. I have not used it so have no idea if
it does everything/anything that you want to do. A student that worked
for us claimed that it did work is all I know. I appreciate your
dilemma. We unload all highly volitle files everynight to overcome
this but we don't have gigs of data to deal with.
#!/usr/bin/awk -f
# Typical usage: "./readbackup /dev/rmt/0m"
# This script is intended to read from /dev/rmt/0m and create several files.
# First, it creates a directory for the name of each database on the tape,
# then it puts the schema for the database in a file called "dbschema" in
# the new directory. For each table with a non-zero number of rows in the
# table, a file is created with each row of the table in it.
# This pattern is designed to pick out the name of the database from
# the archive. It should match on the first line, and only the first
# line, of the archive. It deletes any existing directory for the
# database, and then creates a new one and assigns the name of the schema
# file.
$1 == "{" && $2 == "DATABASE" {
database = $3;
system("rm -rf " database); # Hammer existing directory.
system("mkdir " database); # Create new directory.
schemafile = database "/dbschema.sql";
}
# This pattern is supposed to pick out the name of each table, and assign
# the variable outfile, which is the name of the file that the data for
# the table is supposed to go, correctly.
$1 == "create" && $2 == "table" {
# If $3 = "informix".faculty, name[1] = "informix", name[2] = faculty
split($3,name,"\\."); # The `.' matches any character unless escaped.
outfile = database "/" name[2];
system("rm -f " outfile); # Make sure we will create a new file.
}
# This pattern prints all the lines between "*** load table ***" and
# "*** end of table ***" to the file assigned to the variable `outfile.'
# Hopefully outfile is assigned the name "database/tablename" in the previous
# pattern. The delimiters are discarded.
$0 == "*** load table ***" {
getline; # Discard the "*** load table ***" line.
while ($0 != "*** end of table ***") {
print >> outfile;
getline;
}
getline; # Discard the "*** end of table ***" line.
}
# This pattern prints out every line that isn't between a "*** load table ***"
# and "*** end of table ***" to the schemafile. Note that this includes
# lines also matched by patterns 1 and 2. Pattern 3 (table data) doesn't
# get printed because it executes an explicit `getline' to move through
# the file until "*** end of file ***" is seen.
$0 != "*** end of dbexport ***" { print >> schemafile }
Dick
:wq!
s