Very nice.

I have a shell script I run that sends an email on restore error
(posted it here a while back) and I like this one too. Will have to
give it a go and perhaps tweak it for my use.

- Lou

--- In firebird-support@yahoogroups.com, "Nigel Weeks" <nweeks@...> wrote:
>
>
> > Is there a way of a smart backup to ensure that I can restore in the
> > event of failures.
> >
>
> Heck yeah!
>
> 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.
>
> ========== Start of listing of 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"
>
>
> ============= End of listing of fb_backup.sh =================
>
>
>
> 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
>
>
>
> nweeks@dungeon:~/bin> ./fb_backups.sh
> Backing up 'advopenhome.fdb' to
> '/usr/local/www/data/fb_backups/advopenhome.fdb.fbk'
> 29.56 real 8.81 user 0.89 sys
> Attempting restore of
'/usr/local/www/data/fb_backups/advopenhome.fdb.fbk'
> to temporary database 'localhost:/u1/db2/temp_restore.fdb'
> 75.51 real 13.55 user 7.80 sys
> Restore of '/usr/local/www/data/fb_backups/advopenhome.fdb.fbk' was
> successful
> Listing tables in restored database
> Database: localhost:/u1/db2/temp_restore.fdb, User: sysdba
> CDMNKY_APP CDMNKY_FIELDS
> CDMNKY_NAVLINKS CDMNKY_NAVMENU
> CDMNKY_NAVMENULINKS CDMNKY_SUBFORMS
> CDMNKY_TITLES REPL$CDMNKY_APP
> REPL$CDMNKY_FIELDS REPL$CDMNKY_NAVLINKS
> REPL$CDMNKY_NAVMENU REPL$CDMNKY_NAVMENULINKS
> REPL$CDMNKY_SUBFORMS REPL$CDMNKY_TITLES
> REPL$TBL_AGENT REPL$TBL_CONTACT
> REPL$TBL_PROP REPL$TBL_PUBLICATION
> REPL$TLKP_PRICECOMM REPL$TLKP_SUBURB
> TBL_AGENT TBL_AGENTFILE
> TBL_CONTACT TBL_PROP
> TBL_PUBLICATION TLKP_PRICECOMM
> TLKP_SUBURB
> Dropping temporary database
> Database: localhost:/u1/db2/temp_restore.fdb, User: sysdba
> Compressing '/usr/local/www/data/fb_backups/advopenhome.fdb.fbk' to
> '/usr/local/www/data/fb_backups/advopenhome.fdb.fbk.gz'
> compressed uncompr. ratio uncompressed_name
> 869149 4208128 79.3%
> /usr/local/www/data/fb_backups/advopenhome.fdb.fbk
> Backing up 'cm_storage.fdb' to
> '/usr/local/www/data/fb_backups/cm_storage.fdb.fbk'
> 10.33 real 2.56 user 0.28 sys
> Attempting restore of
'/usr/local/www/data/fb_backups/cm_storage.fdb.fbk' to
> temporary database 'localhost:/u1/db2/temp_restore.fdb'
> gbak: ERROR: attempt to store duplicate value (visible to active
> transactions) in unique index "RDB$PRIMARY2"
> gbak: Index "RDB$PRIMARY2" failed to activate because:
> gbak: The unique index has duplicate values or NULLs.
> gbak: Delete or Update duplicate values or NULLs, and activate
index with
> gbak: ALTER INDEX "RDB$PRIMARY2" ACTIVE;
> action cancelled by trigger (3) to preserve data integrity
> -Cannot deactivate primary index
> 32.65 real 2.95 user 2.87 sys
> !!! RESTORE FAILED !!!
> Listing tables in restored database
> Database: localhost:/u1/db2/temp_restore.fdb, User: sysdba
> CM$XMLDESIGN CM$XMLKEYS
>
> Dropping temporary database
> Database: localhost:/u1/db2/temp_restore.fdb, User: sysdba
> Compressing '/usr/local/www/data/fb_backups/cm_storage.fdb.fbk' to
> '/usr/local/www/data/fb_backups/cm_storage.fdb.fbk.gz'
> compressed uncompr. ratio uncompressed_name
> 116694 1719808 93.2%
/usr/local/www/data/fb_backups/cm_storage.fdb.fbk
>
>
>
> WooHaa! It appears I have a database that can't be restored! Lucky this
> script was around!
>
> Firebird release team: Feel free to include this in Unix releases if you
> want to.
>
> Nige.
>