Posted by: Anonymous Coward
on May 29, 2007 04:41 PM
Wow: database maintenance is now mainstream!
Anyone running any form of CMS/CRM or Wiki should take note of this article (and maybe even read my comments)
Everyone should be aware that doing a plain database file copy *may work* on certain databases even while the database server is still running, if there is no activity on the database, all writes have been flushed to disk, and the file system allows read access to the database file. This is very dangerous because all the assumptions may not be valid during the lifetime of the copy process (for a large database this can easily be several minutes).
Most RDBMS's come with command-line tools to do live backups, essentially a script to start a new transactional snapshot, get a copy of full metadata, then get a copy of full data typically ignoring in-limbo or deleted transactions / content. If this live-backup fails, it may indicate on-disk errors (due to various reasons) and this should be a trigger for the database maintainer to prepare for pre-disaster recovery.
So a regular live-backup scheduled and run automatically but upon failure notifying some administrator is invaluable.
Restore-testing is also prudent, since a "successful backup" is only really worth anything if it can be restored, so modify your backup script to not only do a backup, but also a restore to a temporary file (immediately) to see if the backup file is uncorrupted. If this process is successful, delete the "temp restored database", and make sure you make an off-site copy of the backup, otherwise you're not insured against disk failure.
I hope someone may find this addition useful, and I apologise if I offended anyone in posting this.
Learn about Linux
Download Linux
Get Linux help
Additionally... if I may... this is crucial
Posted by: Anonymous Coward on May 29, 2007 04:41 PMAnyone running any form of CMS/CRM or Wiki should take note of this article (and maybe even read my comments)
Everyone should be aware that doing a plain database file copy *may work* on certain databases even while the database server is still running, if there is no activity on the database, all writes have been flushed to disk, and the file system allows read access to the database file. This is very dangerous because all the assumptions may not be valid during the lifetime of the copy process (for a large database this can easily be several minutes).
Most RDBMS's come with command-line tools to do live backups, essentially a script to start a new transactional snapshot, get a copy of full metadata, then get a copy of full data typically ignoring in-limbo or deleted transactions / content. If this live-backup fails, it may indicate on-disk errors (due to various reasons) and this should be a trigger for the database maintainer to prepare for pre-disaster recovery.
So a regular live-backup scheduled and run automatically but upon failure notifying some administrator is invaluable.
Restore-testing is also prudent, since a "successful backup" is only really worth anything if it can be restored, so modify your backup script to not only do a backup, but also a restore to a temporary file (immediately) to see if the backup file is uncorrupted. If this process is successful, delete the "temp restored database", and make sure you make an off-site copy of the backup, otherwise you're not insured against disk failure.
I hope someone may find this addition useful, and I apologise if I offended anyone in posting this.
Donald Klopper
donald.klopper@gmail.com
#