Sybase cumulative backup vs traditional log backup

[Former Member]

In ASE 15.7 SP100 or higher versions, cumulative backups are supported. My question is, can both cumulative backup and log backup run at the same time for a database?

For example, say, at time t1, we have a completed full backup (full1) of database foo, and

at time t2, we have the first log backup completed, log1.

Now, a cumulative backup is running, and another log backup (log2) starts, and finishes at time t3 while the cumulative backup is still running.

The cumulative backup (cml1) finishes at time t4.

Another log backup (log3) finishes at time t5.

Is it a valid scenario (to let log2 and cml1 run at the same time), or does an application (which performs Sybase backups using 3rd party API) need to prevent such concurrent cumulative and log backups?

If it is valid to run both concurrently (does not result in data corruption), then

• to restore the database up to a time between t2 and t3, we will use full1 + log1 + log2 upto the target time.

• to restore the database up to a time between t4 and t5, we will use full1 + cml1 + log3 upto the target time.

Please correct me if I am wrong.

Thanks,

Ali

[tilman_model-bosch · Accepted solution]

Dear Mr Razib
I have not tested cumulative dump together with concurrent dump transaction (only together with full dumps) but ASE should allow as fars as I know. (it will not allow the truncastion of the log while a database dump is running, though)
'enable concurrent dump tran' has to be set to 1 .

Your load seqeunce looks correct to me

With kind regards

Tilman Model-Bosch

[Former Member · Accepted solution]

Thanks!

So this means cumulative dump and dump transaction are mutually exclusive, so no harm running them together as long as 'enable concurrent dump tran' is set.

Appreciated,

Ali

[tilman_model-bosch · Accepted solution]

Dear Mr Razib,

Full and cumulative database dumps and transaction log dumps are meant to be used together so to get a fully recoverable archive of a database which is recoverable up to the last minute.

When  'enable concurrent dump tran' is set then ASE allows you to run transaction log dumps even when a (full or cumulative) dump is running.

This was not possible in earlier ASE versions. However, if you cant run a transaction log dump  while a database dumps is running this bears the rsik of loosing transactional data in case of a disk failure while database dump is running.

When 'enable concurrent dump tran' is not set (i.e. the feature is disable) ASe will thrwo an error when you attempt to run a transaction log dump while a (full or cumulative) database dump is running.

In any case you can omit to load any cumulative dumps and just rely on a sequence of a full plus all transaction log dumps - even if you took cumulative dumps at runtime.

With kind regards

Tilman Model-Bosch

[Former Member · Accepted solution]

OK thanks again -- this clears up a bit more. So to summarize what you are saying, when 'enable concurrent dump tran' is set

• full dump and transaction log dump

• cumulative dump and transaction log dump

Both of these can run concurrently, and the following are not supported:

• full dump and cumulative dump

• multiple full dumps

Correct?

[tilman_model-bosch · Accepted solution]

Dear Mr Razib,

• full dump and transaction log dump

• full, cumulative dump and transaction log dump

Both of these approaches are ok and will allow you to restore a database including rollforward of transaction to the desired point in time. (cumulative dumps only will not work of course, you always need a full dump as a starting point). These backup strategies are suitable for a production system.

Wheras these backup strategies :

• full dump and cumulative dump

• multiple full dumps

are ok and supported , too, but it will not allow you to restore a database to any desired point in time. The point of recovery will in any case be the time of the last full or cumulative dump.

Taking only database dumps (full or cumulative) is propbaly not suitable for production systems. But this is a decision the system owner has to take.

With kind regards

Tilman Model-Bosch

[Former Member · Accepted solution]

Hello,

I have another couple of questions about cumulative and log backups.

As I understand, when cumulative dump is enabled on a database, the database maintains a list of pages to be dumped, and this list of pages is dumped during cumulative backup. My question is: does cumulative dump also back up transaction logs? I mean, does the list of pages (maintained for cumulative dump) include both data and log devices?

Another question: when performing a 'dump transaction' command, does it back up only the logs created before the 'dump transaction' command started, or does it also back up the logs added after the 'dump transaction' command started?

Thanks,

Ali

[former_member188958 · Accepted solution]

1) The list is actually of allocation units (blocks of 256 pages) rather than pages.  The cumulative dump does include pages allocated to syslogs; the list tracks dirtied allocation units in both data and log devices.  (This list is the "sysdams" system table).

2) Essentially only up to when the DUMP TRAN command started.  DUMP TRAN issues a checkpoint and then dumps the log up to and including the page containing that checkpoint record.  It is possible that a few small transactions might be written after the checkpoint record on the same page and get included in the dump.   In contrast, the DUMP DATABASE command includes changes made by transactions while it is running up until a point very near the end of the dump process.

[Former Member · Accepted solution]

Thanks Bret.

A follow up question about #1 in your reply: So when a cumulative dump starts, does it also issue a checkpoint for logs dump? Or, does cumulative dump behave like 'dump database' and include logs changes up until "very near the end of the dump process"?

[former_member188958 · Accepted solution]

Cumulative DUMP is more like the traditional dump database, though Cumulative DUMP does start by issuing a checkpoint and traditional dump database does not.  The general process is

a) Checkpoint to flush all dirty pages to disk

b) Start dump flush list (list of pages modified while pages are being copied)

c) Copy all pages that have been modified since the full dump

d) Block non-logged writes and copy the remaining pages in the DFL

e) Determine the dump instant

f) Copy the pages for the active log from the oldest active xact in the ckpt at a) and the dump instant at d)

-bret

[jgleichmann]

just for the summary if someone needs more information on this topic:

2005989 - Dump and Load Performance Tuning

Dump and Load Sequences

Cumulative Dump