How dump database cumulative works?

[former_member280303]

Hi,

I have some doubts about how the dump database cumulative command works,

I guess that the changes perfomed after a dump database full are registered ( not in the syslogs table) and when you send a dump database cumulative ase backup those changes.

I want to know if I can use this command in a database that has the options "trunc log on chkpt" and "select into/bulkcopy/pllsort" set?

Regards,

Luz Rodriguez

[victoria_normand · Accepted solution]

Hi,

Just for the record (for others people reading the thread ), the  "trunc log on ckpt" and "select into/bulkcopy/pllsort" database options shouldn't be enabled when using ASE on SAP Business Suite environment, which are the default settings actually (see also SAP Note 1581695 and 1749935 - Configuration Guides for SAP ASE 16 and 15.7 ). You then can use cumulative database dump option, just be sure to set database option "allow incremental dumps". To check the history of a cumulative dump using sp_dump_history
More information about cumulative database dumps: http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc00641.1570100/doc/html/ate1345402438...

Regards,

Victoria.

[former_member280303 · Accepted solution]

Hi Victoria,

I am undestanding that If I want to use the cumulative dumps i need to turn off "trunc log on ckpt" and "select into/bulkcopy/pllsort" ?

Regards,

Luz Rodriguez

[Former Member · Accepted solution]

Hola Luz!

Yes, that is how I understand Victoria's detailed response. Regardless of what the default and recommended settings are for SAP Business Suite, both options should be turned off. Also, remember as she points out, to turn on the "allow incremental dumps" database option.

Saludos,

Jean-Pierre

[former_member280303 · Accepted solution]

Hi Jean-Pierre,

As we have those options set in the database, this new backup strategy can help us to recover more information.

I tested in pubs2 database with same options set and the dump database cumulative command worked.

Thanks for your help

[Former Member · Accepted solution]

Luz,

You tested in pubs2?

I thought you had to have separate data and log fragments for both incremental and cummulative dumps to work???

Otherwise, I think this is what Victoria is saying:

EXEC sp_dboption pubs2, "trunc log on ckpt", FALSE

GO

EXEC  sp_dboption pubs2,  "select into/bulkcopy/pllsort", FALSE

GO

EXEC  sp_dboption pubs2,  "allow incremental dumps", TRUE

GO

Regards,

Jean-Pierre

[former_member280303 · Accepted solution]

I created pubs2 with device separation and it worked.

[victoria_normand · Accepted solution]

Hi,
That's right, both options *should be* disabled for Business Suite environment, it is a requirement .
I emphasized that because the thread is under the "SAP Applications on SAP ASE" community

About initial question if the cumulative dump in a database can be done if the database has the options "trunc log on chkpt" and "select into/bulkcopy/pllsort" set, yes it can be done, dump database will work having that enabled, cumulative database dump exists means since ASE 15.7 SP100 and it support dump for minimally logged operations (select into). You just need to be sure that the database option "allow incremental dumps" is turned on. The "trunc log on ckpt" turned on prevent any dump transaction to be done, but cumulative dump database can be done.

This information link might be interesting for you : http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc00641.1570100/doc/html/ate1345224155...

HTH,
Regards,
Victoria.

[Former Member · Accepted solution]

Cool!

[Former Member]

Luz,

I don't think you can ever perform incremental backups when the "truncate log on checkpoint" options is turned on in any database.

If my understanding of the cummulative database backup functionality in the latest releases of ASE is correct, I believe it relies on the same principles to perform the backup, hence the answer would be, NO, you cannot perform cummulative database backups on a database which has the "truncate log on checkpoint" option turned on.It would be easy enough to create a small database and verify it...

As far as the "select into/bulkcopy/pllsort" option, I don't believe it has ever had a role in the backup process.

Regards,

Jean-Pierre

[former_member280303]

Hi Jean-Pierre,

You are right, I could not perform incremental backups because the option "truncate log on chkpt" is set and we also execute minimal log commands. Now we only perform a dump database full every night.

As the documentation of the new feature explains that the cumulative dumps register the changes. I want to know if those changes are registered in the log or in another place.

Regards,

Luz Rodriguez

[former_member188958]

ASE uses the new SYSDAMS table to track which allocation units have been changed since the last full database dump.  The cumulative dumps back up the changed allocation units.

[former_member280303]

Hi Bret,

The SYSDAMS table is local to each database or is in the master database?

[Former Member]

Luz,

Lets see what Bret says. But I think it must be like SYSGAMS. You probably can't access it like a regular table or view. The probably is some undocumented DBCC command to look at the contents.

Jean-Pierre

[former_member188958]

sysdams is local to each database.

-bret

[Former Member]

Cool1

[sladebe]

I think this comment is incorrect. Cumulative backups keep track of changed data pages whether or not the transaction log has been truncated.