Consultant Field Note: Explaining Cumulative verses Transaction Log Dumps
Recently I was asked a question about Cumulative dumps verses Transaction Log dumps; at first glance aren’t they similar?
I agree, they do seem to do the same thing on the surface however based on the properties of each method this could be a fit in your SAP ASE environment. Before we start to design a new database backup strategy, let us explore what the Cumulative Dump is and how it differs from a Transaction Log dump.
A Cumulative Log dump is a dump command that allows one to copy/save all the modified pages in the database that have altered since the last full database dump. A Transaction Log dump is a dump command that allows one to copy/save all the commands/instructions that potentially affect modified pages in the database that have altered since the last full database dump and/or transaction log dump. The key difference is: Transaction Log Dumps are capturing the transactions while Cumulative Dumps are capturing the changes that occur on the “page” level. To illustrate this concept further, if the Transaction Log does not capture a command fully then it is not recorded in the Transaction Log dump. This is precisely the case in minimally logged operations.
Lets take a typical week of database dumps:
Using Transaction Log Dumps we would do a full database dump on a Sunday. On Monday through to Saturday we would take our daily Transaction Log dumps. If we had a failure on Thursday, then to recover this database we would need to load:
Sunday Full Database Dump + Monday Transaction Log Dump + Tuesday Transaction Log Dump , Wednesday Transaction Log Dump + Thursday Transaction Log Dump
Using Cumulative Dumps we would continue to do a full database dump say on a Sunday. On Monday through to Saturday we would take our daily
Cumulative Dumps. If we had a failure on Thursday, then to recover this database we would need to load:
Sunday Full Database Dump + Thursday Cumulative Dump
The advantages of Cumulative Dumps seems obvious, less time and complexity to recover from a Thursday crash.
The properties of Cumulative Dumps are:
•We can roll forward a full backup of a database without having to back up the entire database.
•Minimal logging changes can be recorded.
•Speed recovery time (we just talked about this above)
•Potentially reduce backup size. Think about read-only tables or archive databases and the ability to only record modified pages.
•Improve backup performance in the case of rapidly changing tables that are a subset of the data.
•One can estimate both the dump and the load time in advance using the sp_dump_info system procedure.
It is possible to have a mix of the old and new dump commands: we can have a full database dump (dump database database_name full), a cumulative dump (dump database database_name cumulative) and a transaction log dump (dump transaction database_name).
Before you start making wide-scale changes to your DBA backup scripts just keep in mind that you can perform a cumulative dump on any database except master and temporary databases. This includes low-durability databases. Further to your design, you should be aware of:
•Dump size will grow from Monday to Saturday (in our example), because every cumulative dump taken includes the previous one.
•For some operations involving massive modified pages (index creation), the size of Cumulative Dumps could be larger than the Transaction Log. We can mitigate this by using the compression option.
•This option is available starting from SAP ASE 15.7 SP100 and there a various sp_configure options that need to be set including the database option of sp_dboption dbname, ‘allow incremental dumps’, true.
•We still need to take care of the Transaction Log size.
It’s an exciting time for this old tried and true dump database command.
This Field Notes Series is dedicated to observations from the field taken from personal consulting experiences. The examples used have been created for this blog and do not reflect any existing SAP Customer configuration.