I think I need to implement a better backup strategy. I'm thinking this:
Every day at 1am: validate db and if ok, full backup to another disk + copy to dat + copy to amazon s3 (encrypted). If validate fail, send me an email.
From 1am to 23:59: incremental backup to another disk + copy to amazon s3.
Keep each backup 6 days (i.e.: every monday I'll overwrite previous monday backup).
Incremental backup, what you think to put events in database to do this, every 15min or 10mb log file - recreating log file.
That is time, my db size is 80.1gb growing about 60mb/day.
Is this a good strategy? Should I change something? What can happen if inc backup is running and another inc backup starts?
Thank you and sorry this too long question. And again, sorry my poor english.
asked 12 Aug '10, 01:42
First I'm glad to hear that you are thinking about proper backup strategies - this is an important part of any production system. Also please make sure that you test your recovery procedure to make sure that your backup procedure is doing what you want it to do!
You should consider not doing your validation of the database on your actively running production system since this can result in spurious validation errors. Alternatively you should backup the database and then validate the backup (by taking a copy of the backup and running dbvalid on the copy). If your system is idle you may consider doing the validation on the production system but you need to be aware of possible spurious errors that may be reported if update activity were to occur during the time that the validations is running. So for your system, if at 1am the database is idle then your plan to validate first may work for you?
How often you run your backup is going to depend on your level of acceptance of lost data if a catastrophic event were to occur where your computer (disk) is totally lost. If you have no tolerance for any lost, then consider doing live backups or setting up a High Availability system
If a backup is already running then an subsequent backup that is attempted will block until the first one completes. i.e. only one backup can be performed at any time.
The SQL Anywhere 12 docs talks more about designing a backup and recovery plan.
This white paper also talks about backup and recovery strategies.
answered 12 Aug '10, 14:56
Just to add to Mark's response and the according comments:
When doing incremental backups, I would make sure to store the log to different files (possibly in a round-robin scheme using a few file names). I.e. you could store the backup log file from 1:00 and 1:15 to different files. This should make sure that a failing backup or file copy operation would not damage your "last good" file.
Note: Your question seems that you use different files names/directories for the daily full backups, but I was not clear about your differential backups.)
Furthermore you could check the validity of the backup logs by running DBTRAN against them, cf. the help.
answered 24 Aug '10, 15:32