No, I don't think your thinking is insane. In fact, here's a shell
script that dumps pipe delimited ascii backups of specified files,
keeping 5 versions worth and compressing them to keep disk space down.
Assuming daily runs, as long as you realize within 5 days, you can
get table data back. For dropped tables, I would switch the Export
to IXF format so you get structure AND data..
You know this, but remember this is not Point In Time (PIT) Recovery!!
Its only as good as the last run. This, combined w/ some audit trail
stuff of mine, has gotten me out of trouble a few times..
Don't know what OS you have. Mine is Linux.
cheers
aj
################################################## ##############
# This script backs up database tables in ascii format
cd /db2/backups/ascii
echo "connect to <MYDB;" /db2/backups/ascii/backup.sql
echo "set schema <MYSCHEMA;" >/db2/backups/ascii/backup.sql
# ************************************************** ***************
for table in <LIST TABLES, SPACE DELIMITED, YOU WANT TO BACK UP>
do
if [ -s $table.5.gz ]
then
rm $table.5.gz
fi
if [ -s $table.4.gz ]
then
mv $table.4.gz $table.5.gz
fi
if [ -s $table.3.gz ]
then
mv $table.3.gz $table.4.gz
fi
if [ -s $table.2.gz ]
then
mv $table.2.gz $table.3.gz
fi
if [ -s $table.1.gz ]
then
mv $table.1.gz $table.2.gz
fi
if [ -s $table.txt.gz ]
then
mv $table.txt.gz $table.1.gz
fi
echo $table
done | /usr/bin/awk '
NF == 1 {
printf("EXPORT TO %s.txt OF DEL MODIFIED BY coldel| chardel\"\"
decpt. decplusblank datesiso MESSAGES %s.msg SELECT * FROM %s
;\n",$1,$1,$1) ;
}' >/db2/backups/ascii/backup.sql
echo "connect reset ;" >/db2/backups/ascii/backup.sql
db2 -tvf /db2/backups/ascii/backup.sql
################################################## ##############
Troels Arvin wrote:
Hello,
I've run into situations where a table was accidentally dropped. The
related database contained lots of other tables in lots of other schemas
used by many different users. So I couldn't simply recover to a previous
point in time (the other users' changes would be lost and/or there would
be periods of no database availability).
It seems that my only option was a complete, redirected restore. (Compete,
because redirected restore operations can't operate on selected
tablespaces). As some of our databases are very large, a complete,
redirected restore takes a lot of time.
This turns out being a problem.
What are my options? I'm considering supplementing our regular (TSM)
online backup scheme with daily dumps to raw files, based on EXPORT and
db2look. With a little text massage of the dumps, I would be able to
restore selected tables without going through RESTORE. - And in some cases
this would be fine because the dropped table(s) might not have been
updated recently. Is my thinking (in)sane?
