backup with autolog - strategy for overwrite log files

[Former Member]

Hello together,

i 've a question regarding the backup strategy of my SAP DB. I created some backup media to do a full, incremental and log backup, activated autolog and all went fine.

For complete and incremental backup media, I enabled the overwrite flag, so I have one file containing the complete backup of a week and four files containing the incremental backups of each weekday.

Now I faced the problem that the autologs are always added because the autolog media is not enabled for overwrite.

My question is: What is the best strategy to "save" disc space without manually deleting older autolog files?

Should I enable overwrite for autolog as well? What does this mean in case of restoring the database?

Any help and hints are welcome!

[roland_mallmann · Accepted solution]

Hi,

well... using the overwrite option of both the incremental and full backup media (called backup templates from version 7.7 onwards) in your case means that you can go back one week at max during a recovery. You must decide for yourself if that's okay for your business.

If you specified the incremental backup to overwrite itself, howcome you have 4 of them at the end of the week? I'd say you'd have one.

Another thing to consider is that you'd really not have much alternatives in case your backups are unusable, for example due to hardware issues.

There is no option to overwrite existing logbackups, as you've probably noticed they're written sequentially, one after the other (example: LOG.001 is followed by LOG.002).

Regards,

Roland

[Former Member · Accepted solution]

Hey Roland,

thx for your answer. Maybe I did not specify my current setup enough, so let's try again.

I created 4 different media locations for my incremental backup, so each time only one of them is overwritten each day. -> Means I can go back 4 days.

Each friday a complete backup runs which than can backed up to a tape. So far, that's ok for me.

My problem is: When I create an incremental backup each day, how should the autolog be configured to extend automatically up to a given value (e.g. 2 log volumes)? In my opinion, I only need autolog files up to my last incremental. Is that right?!

If not, how would I restore a system which crashed in the afternoon?

Edited by: Andreas Krienke on Jun 15, 2009 3:19 PM

[roland_mallmann · Accepted solution]

Hi,

yeah, I figured as much with your 4 incremental backups.

Regarding the Autolog: it saves the log at a certain fixed logfilling level, no logvolume is added. If enough loginformation is being created, you'll get several backups, all triggered automatically. These enable you to specify where you'd want to recover to, in case you ever need to do a recovery.

If your system crashed on an afternoon, you would recover the last full data backup, check which incremental data backup you need, recover it and then which logbackups need to be applied and recovered. In case you want to go back to the last point in time, recover all available logbackups (ofcourse starting with the one created after the full data backup used initially for this recovery).

If you'd use either our Database Manager GUI (short: DBMGUI) or our new tool Database Studio, the Recovery Wizard would guide you through these steps.

Regards,

Roland

[Former Member · Accepted solution]

Hi,

thx again. Think I got the point, so only for clarification: To restore a special point in time, I have to restore the complete backup, afterwards the incremental(s) and then all the autologs after(!) the last incremental that I use. Is that correct?!

If so, can I delete autolog files that are older than my last incremental backup?! Because this is what I wanted to do: save space by deleting not needed log files or the configure the DB to overwrite older ones.

Edited by: Andreas Krienke on Jun 15, 2009 4:09 PM

[roland_mallmann · Accepted solution]

Hi Andreas,

please note the clear distinction between logbackups and logvolumes. In order to free up space from the logvolumes, log backups are necessary. The autolog option thus does not autoextend the volumes, but creates logbackups. These are written as files to your filesystem.

I understand that you'd like to save space, but I'd not recommend to delete as much as possible (with regard to MaxDB or database backups in general). Being able to go back only one week and only having a single copy of every backup is not really optimal.

Regarding your question: you'd at least need all the logbackups created after the last full data backup (thus from the previous friday in your example) for the following week, until your next full data backup ran on the following friday (and was successful).

It's also a good idea to check your backups regularly, most conveniently done with Database Studio.

Regards,

Roland

[Former Member · Accepted solution]

Hey Roland,

thx for the hints. I think I mixed some terms so sorry for that. Of course I know the difference between log volumes and log backups.

Although your other point is totally clear I knew how to perform a "complete" backup strategy (e.g. father-grandfather principle) and left that out for my current problem.

What I want is to reduce space on the partition of the log backups as I wondered why I should hold all of them in my system. Cannot imagine that every sap system waste that much space for log backups as they also will perform complete backups regularly as well.

Hope you got my point that I do not want to skip around a good backup strategy but only wanted to face the autolog backup space problem here!

[roland_mallmann · Accepted solution]

Hiho,

I stated that you needed all the logbackups from after your full data backup, but those created after the last incremental would technically suffice, BUT the big pluspoint would be that you'd have an alternative recovery strategy (fulldatalogs & fulldatainc+logs), that's why I recommended it. Please excuse me for not being clear enough on this point.

Regarding the father-grandfather principle: you save (i.e. 'archive') your backups also to tape?

We've also got options to manually or automatically (using SAP CCMS and third pary tools) archive log backups. You can find out more on this subject in our online [documentation|http://maxdb.sap.com/doc/7_7/44/d97af17a012754e10000000a1553f6/content.htm].

Regards,

Roland