System Changes Supporting Cumulative Dumps

Before you use the cumulative backup feature, enable a database to maintain the list of pages that need to be dumped. The sp_dboption includes this parameter, where dbname is the name of the database:

sp_dboption dbname, 'allow incremental dumps', true

You can also configure Adaptive Server to enable concurrent transaction dumps and cumulative database dumps:

sp_configure 'enable concurrent dump tran', 1

You can execute concurrent dump transaction commands, but you cannot execute concurrent dump database (full) commands.

You can record cumulative dump commands in the dump history file:

sp_configure 'enable dump history', 1

The sp_dump_history system procedure now includes the cumulative keyword in its @dump_type parameter, which supports all the functionality for cumulative dumps as it does for full database dumps. For example, run:

dump database mydb full to "/dev/db1.dmp"
dump tran mydb to "/dev/dt1.dmp"
dump tran mydb to "/dev/dt2.dmp"
dump database mydb cumulative to "/dev/dc1.dmp"
dump tran mydb to "/dev/dt3.dmp"

Then execute:

load database mydb with listonly=load_sql

The result is:

load database mydb full from "/dev/db1.dmp"
load database mydb cumulative from "/dev/dc1.dmp"
load tran mydb from "/dev/dt3.dmp"

Since the load sequence you get is the simplest one, if there are cumulative dumps, you need not use any transaction dump generated between the full database dump and the cumulative dump.

The sp_dump_info system procedure estimates the size of data and log that a cumulative dump contains at a specific point in time. The size is reported in units of pages, KB, MB, or GB as appropriate, and may be slightly smaller than the actual size of the archive file (or files, if you are using multiple stripes), because the archive contains some additional information by way of the labels, header, trailer, and run list pages. By default, sp_dump_info assumes the dump is uncompressed. If it is compressed, the archive size is smaller than that reported by sp_dump_info. A sample output for sp_dump_info:

 sp_dump_info test
go
 Data    Log  Database percentage Allocation threshold
 ------- ---- ------------------- --------------------
 4368 KB 2 KB                   2                   40
(return status = 0) (return status = 0)

The output indicates that a cumulative dump occurring at this point in time, would contain approximately 4368KB of data and 2KB of log, representing 2 percent of the total database size.

Compare this with the size of an actual cumulative dump of the archive file:

 dump database test cumulative to "c:/tmp/test.dmp"
go
Backup Server: 4.171.1.1: The current value of 'reserved
pages threshold' is 85%.
Backup Server: 4.171.1.2: The current value of 'allocated
pages threshold' is 40%.
Backup Server session id is: 10. Use this value when
executing the 'sp_volchanged' system stored procedure
after fulfilling any volume change request from the Backup
Server.
Backup Server: 6.28.1.1: Dumpfile name 'test122480F0EF'
section number 1 mounted on disk file 'c:/tmp/test.dmp'
Backup Server: 4.188.1.1: Database test: 4328 kilobytes
(3%) DUMPED.
Backup Server: 3.43.1.1: Dump phase number 1 completed.
Backup Server: 3.43.1.1: Dump phase number 2 completed.
Backup Server: 3.43.1.1: Dump phase number 3 completed.
Backup Server: 4.188.1.1: Database test: 4370 kilobytes
(3%) DUMPED.
Backup Server: 3.42.1.1: DUMP is complete (database test).

The corresponding size of the archive is 4487168 bytes, or 2191 pages. This differs from the estimate given by sp_dump_info by 29 pages (58KB), which is the result of 8 pages for the dump labels, 1 page for the dump header, 1 page for the dump trailer and 19 pages containing run lists. The size of the dump labels, header, and trailer are independent of the numbers of pages dumped, while the number of pages used by run lists is dependent on the numbers of pages dumped.