07 and backups - do not trust backupexec

[Doug Easterbrook]

hi Wendy, and the world.

I'll try to be plain and simple with this.

NO BACKUP TOOL IN THE WORLD SHOULD BE TRUSTED TO BACKUP A LIVE DATABASE.

there... its off my chest.


Why?   If any database:
- is in use, be it omnis .df1, oracle, mysql, postgres (doesn't matter)  AND
- it being used by the user AND
- any backup tool backs it up while in use (even if it has the ability to do open files)

then your backup is hopelessly corrupt.   It will either be:
a) physically corrupt OR
b) logically corrupt OR
c) both

and definitely USELESS.


The only recommended way of doing backups for any database is:
a) make a snapshot of the database
b) backup the snapshot
c) make the backup program avoid the directory containing the live database.


For postgres, we do:
- pg_Dump to a file and back up the file

With open base, we
- ran the backup utility in openbase to get a snapshot, then saved that

when we did omnis df1, we recommended;
- stopping omni (we used Kelly's lights out to force people out of the app).
- or stopping the data bridge if you are using that - which make is nicer
- using winzip or some task to ZIP the file(s) to a backup
   (that way all the pieces of the df1, df2, df3, etc are in one file)
- backup the zip file and avoid the data bridge or omnis directory.




and why is a backup useless unless you use a tool designed for snapshots?   If you start a backup on the df1, then by the time the df1 is halfway saved to the backup, a use could change the df2, or another protion of the df1, and the indexes get changed.   Those changes don't make it to the backup.

and if you try to restore, index errors, free block errors, unable to read some records (physical corruption) not to mention that the backup has half the changes in it (logical corruption).

its why we like postgres and pg_dump.   The database design snapshots the records that make up a logically correct backup and start saving those --- and you can continue to run - secure in the knowledge that you have a logically correct backup - even though changes are being mad 24x7.

I think oracle is the same way.

Openbase forces the world to stop while the backup is being made

Not sure why mysql does.




anyway..  it doesn't really matter - you cannot trust any backup tool (backupexe, retrospect, you name it) to give you a logically correct database.


now, I step down from my soapbox.    This is, by far, the biggest issue I have faced in 35 years in the database world - because we find customers don't think this way and just assume a copy of the live database is good enough.   It hardly ever is and can hardly ever be restored.   And I have to explain why, then pull the rabbit out of the hat, and then explain why its their fault and so they have to pay for the hours it takes to recover their data.


hope this helps.


always make a snapshot, then backup the snapshot



Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 536-1205    Fax (403) 536-1210

On Jan 17, 2010, at 10:00 AM, omnisdev-en-request at lists.omnis-dev.com wrote:

> Hi all
> 
> I am after picking brains again.
> 
> A customer has outsourced their IT support - this company says they use  
> 'Backupexec' to create 'snapshots' of the database.
> 
> Does any one know if this program would a) create a proper backup if  
> someone was still logged into the database and b) could it be possible for the  
> backup routine to corrupt the database if it could not create a proper  
> backup?
> 
> Many thanks for your help