|
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. |
|
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. @mark thank you. I'll test this solution today. 7
Here's a good reason to validate the backup copy rather than the main database: If the backup copy passes validation then BY DEFINITION the main database would have passed validation at the point the backup was made. However, if you validate the main database before the backup, you DO NOT KNOW if the backup passes validation... it could have been corrupted during the backup process. It is MORE IMPORTANT to know you have a valid backup, than to know your main database is valid. The latter is very important, of course, but not AS important. 1
Having said that, there was some discussion at Techwave of streamlining validation of the live database, since frequent/continuous validation may be desirable in some applications. Currently the false positives problem is a showstopper. 1
@Breck - While I agree about the desirability of validating the backed-up database, it's a lot more complex to achieve for a small end user (you know, the ones with no dba yadda yadda....) than validating before backup. With more and more small users wanting their databases available 24 x 7 because of web integration, a way of validating a running db becomes more and more desirable. It came up at a user meeting in Frankfurt a few months ago as well. |
|
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. |
