Sybase cumulative/incremental dump and load

There is one option named 'allow incremental dumps', by default not set, that would allow to perform incremental backups of a database.

Incremental backup contains only datapages that were modified since the last full database backup. This allows to build more compact recovery scenarios. For a huge database, where users usually don't perform huge updates, it may be easier to perform full backup once a week and an incremental backups everyday.. If required the transaction dumps can be done as usually.

Let's examine an example of incremental backup. In the code below: we create the devices and database, change option allowing incremental dumps for the database and perform one full backup, two incremental and finally one log dump:

USE master
GO

DISK INIT NAME ='myDevice_A', 
          PHYSNAME = '/opt/sap/databases/myDevice_A.dat', 
          SIZE=10240
GO

DISK INIT NAME ='myDevice_B', 
          PHYSNAME = '/opt/sap/databases/myDevice_B.dat', 
          SIZE=2560, 
          SKIP_ALLOC = true
GO

CREATE DATABASE myDB
ON myDevice_A = '20M'
LOG ON myDevice_B = '5M'
GO

sp_dboption 'myDB','allow incremental dumps', 'true'
GO

USE myDB
GO

CREATE TABLE T1(id INT)
GO

INSERT T1 VALUES (1)
GO

DUMP DATABASE myDB TO '/home/dumps/01_myDB_full.bak' 
GO

INSERT T1 VALUES(2)
GO

DUMP DATABASE myDB CUMULATIVE TO '/home/dumps/02_myDB_cumulative.bak'
GO

INSERT T1 VALUES(3)
GO

DUMP DATABASE myDB CUMULATIVE TO '/home/dumps/03_myDB_cumulative.bak'
GO

INSERT T1 VALUES(4)
GO

DUMP TRAN myDB TO '/home/dumps/04_myDB_tran.bak'
GO

USE master
GO

Now we will try different restore scenarios. The first just restores the full backup. No surprise, only one record will be visible in the restored table.

--TRIAL 1 -- only the full backup restored (1)

DROP DATABASE myDB
GO

CREATE DATABASE myDB
ON myDevice_A = '20M'
LOG ON myDevice_B = '5M'
FOR LOAD
GO

sp_dboption 'myDB','allow incremental dumps', 'true'
GO


LOAD DATABASE myDB FROM '/home/dumps/01_myDB_full.bak'
GO

ONLINE DATABASE myDB
GO

SELECT * FROM myDB.dbo.T1
--only the record '1' returned

So, let's try to restore the full + the first cumulative. The result should be that the test table will contain 2 records:

--TRIAL 2 -- only the full backup + cumulative restored (1+2)

DROP DATABASE myDB
GO

CREATE DATABASE myDB
ON myDevice_A = '20M'
LOG ON myDevice_B = '5M'
FOR LOAD
GO

sp_dboption 'myDB','allow incremental dumps', 'true'
GO

LOAD DATABASE myDB FROM '/home/dumps/01_myDB_full.bak'
GO

LOAD DATABASE myDB CUMULATIVE FROM '/home/dumps/02_myDB_cumulative.bak' 
GO

ONLINE DATABASE myDB
GO

SELECT * FROM myDB.dbo.T1
--the records '1' and '2' returned

The next test can be to restore the full backup and the last incremental. We should have 3 records:

--TRIAL 3 -- only the full backup + last cumulative restored (1+3)

DROP DATABASE myDB
GO

CREATE DATABASE myDB
ON myDevice_A = '20M'
LOG ON myDevice_B = '5M'
FOR LOAD
GO

sp_dboption 'myDB','allow incremental dumps', 'true'
GO

LOAD DATABASE myDB FROM '/home/dumps/01_myDB_full.bak'
GO

LOAD DATABASE myDB CUMULATIVE FROM '/home/dumps/03_myDB_cumulative.bak' 
GO

ONLINE DATABASE myDB
GO

SELECT * FROM myDB.dbo.T1
--the records '1', '2' and '3' returned

Of course using the incremental dumps/load can be finished with transaction load. Now we expect to have 4 rows:

--TRIAL 4 -- the full backup + last cumulative + transaction dumps restored (1+3+4)

DROP DATABASE myDB
GO

CREATE DATABASE myDB
ON myDevice_A = '20M'
LOG ON myDevice_B = '5M'
FOR LOAD
GO

sp_dboption 'myDB','allow incremental dumps', 'true'
GO

LOAD DATABASE myDB FROM '/home/dumps/01_myDB_full.bak'
GO

LOAD DATABASE myDB CUMULATIVE FROM '/home/dumps/03_myDB_cumulative.bak' 
GO

LOAD TRAN myDB FROM '/home/dumps/04_myDB_tran.bak' 
GO

ONLINE DATABASE myDB
GO

SELECT * FROM myDB.dbo.T1
--all the records returned: '1', '2', '3' and '4'

The last test validates what would happen if by mistake more than one cumulative dump would be loaded. The answer is - nothing!

--TRIAL 5 -- all the backups restored (1+2+3+4)

DROP DATABASE myDB
GO

CREATE DATABASE myDB
ON myDevice_A = '20M'
LOG ON myDevice_B = '5M'
FOR LOAD
GO

sp_dboption 'myDB','allow incremental dumps', 'true'
GO

LOAD DATABASE myDB FROM '/home/dumps/01_myDB_full.bak'
GO

LOAD DATABASE myDB CUMULATIVE FROM '/home/dumps/02_myDB_cumulative.bak' 
GO

LOAD DATABASE myDB CUMULATIVE FROM '/home/dumps/03_myDB_cumulative.bak' 
GO

LOAD TRAN myDB FROM '/home/dumps/04_myDB_tran.bak' 
GO

ONLINE DATABASE myDB
GO

SELECT * FROM myDB.dbo.T1
--all the records returned: '1', '2', '3' and '4'. No errors recorded

Note:

• to perform cumulative dump the option 'allow incremental dumps' must be set
• when restoring the database the option does not need to be set, however if you plan to perform cumulative dumps, you must to change the option once again after the restore
• cumulative dump requires to perform the full backup first. It is not possible to have cumulative dump without the full first
• when restoring the database, you need to start with the full backup, the last cumulative dump and then the transaction logs

To finish our tests, let's do some cleanup. Don't forget to remove the devices' files from operating system!

--CLEANUP

DROP DATABASE myDB
GO

dbo.sp_dropdevice 'myDevice_A'
GO

dbo.sp_dropdevice 'myDevice_B'
GO