Duleep wrote:
I have had problems restoring some client databases due to corruption (Had to use data pump on many occasions)
Is there a way of a smart backup to ensure that I can restore in the event of failures?
Nigel Weeks answers:
Here's a short shell script to do the job. I liked the idea of restoring each DB, just to make sure everything worked ok. Sure, it'll increase the time taken to do backups, but it's probably worth it.
fb_backups.sh
#!/bin/sh
########################################################
#
# Tweak these to suit your system
#
########################################################
db_loc="/u1/db"
backup_loc="/usr/local/www/data/fb_backups"
restore_db="localhost:/u1/db2/temp_restore.fdb"
db_user="sysdba"
db_pass="masterkey"
########################################################
#
# You shouldn't need to change anything below this line
#
########################################################
# Move to the database directory
cd ${db_loc}
for i in `ls *.fdb`
do
echo "Backing up '${i}' to '${backup_loc}/${i}.fbk'"
# Backup each database to a file
time gbak -b -t -user ${db_user} -pass ${db_pass} localhost:${db_loc}/${i} ${backup_loc}/${i}.fbk
# Attempt to restore each database to a temporary database
echo "Attempting restore of '${backup_loc}/${i}.fbk' to temporary database '${restore_db}'"
time gbak -r -user ${db_user} -pass ${db_pass} ${backup_loc}/${i}.fbk ${restore_db} && echo "Restore of '${backup_loc}/${i}.fbk' was successful" || echo "!!! RESTORE FAILED !!!"
# Peek inside the temporary database
echo "Listing tables in restored database"
echo "show tables;" | isql -u ${db_user} -p ${db_pass} ${restore_db}
# Drop the temporary database
echo "Dropping temporary database"
echo "drop database;" | isql -u ${db_user} -p ${db_pass} ${restore_db}
# Compress each backup file
echo "Compressing '${backup_loc}/${i}.fbk' to '${backup_loc}/${i}.fbk.gz'"
gzip -f ${backup_loc}/${i}.fbk
gzip -l ${backup_loc}/${i}.fbk.gz
done
echo "All database backups completed"
When run, it produces the following output, including:
- time taken to backup
- time taken to restore
- All tables in the restored DB
- Compression stats in the .fbk file once compressed