As we are aware that, we have different types of Database Backup in SAP(Sybase) ASE inline with other relational databases. In this post, we shall cover types of Backup concerning sp_dboption(database option) and how to use them effectively together while loading the database in order to achieve Point in Time Recovery.
- Full Backup – This is the most basic type of backup and doesn’t require any pre-conditions to be met. We may take backup on one file or multiple file using stripe option, we can also use compress option to reduce the size of backup file as well as improve on duration. This backup has no role in truncating log. For any other backup to be taken base is always full backup. If new database is created and we would like to take cumulative or tran backup, we can’t do so until and unless Full backup is run. Command to take Full backup:
Single File
dump database db_name to “path/db_backup.ext”
Multiple FIle:
dump database db_name to “path/db_backup1.ext” stripe on
“path/db_backup2.ext”
Multiple File with Compression:
dump database db_name to “path/db_backup1.ext” stripe on
“path/db_backup2.ext” with compression = 5
2. Cumulative Backup – Cumulative backup contains all the changes occurred after last full backup, flag gets reset after next full backup. In order to be able to take this type of backup “allow incremental dumps” should be enabled for that database using command given below. Cumulative backup doesn’t truncate log and hence even if we take this backup periodically, there is possibility that log file of database could continue growing.
command to enable incremental dump
sp_dboption db_name, ‘allow incremental dumps’, true
Please note that, after enabling above database option, we need to run full backup before taking incremental(cumulative) backup. You may take multiple cumulative backup between two full dumps. Command to take cumulative backup is as below:
Single File
dump database db_name cumulative to “path/db_backup.ext”
Multiple FIle:
dump database db_name cumulative to “path/db_backup1.ext” stripe on
“path/db_backup2.ext”
Multiple File with Compression:
dump database db_name cumulative to “path/db_backup1.ext” stripe on
“path/db_backup2.ext” with compression = 5
3. Tran Backup – Tran backup contains all the changes occurred after last Tran backup or full backup. In order to be able to take this type of backup “trunc log on chkpt” should be disabled for that database using command given below. Tran backup truncates log and hence should be taken periodically, there is possibility that log file of database could continue growing even if we are taking Tran backup only if there is an active open transaction. Tran backup contains all committed and non-committed transaction upto that point of time when tran backup was being taken. While restoring uncommitted transactions are rolledback whereas in the sequential restore they are rolled forwarded and brings data to consistent state along with time.
command to enable tran dump
sp_dboption db_name, ‘trunc log on chkpt’, false
Command to take Tran Backup:
Single File
dump tran db_name to “path/db_backup.ext”
Multiple FIle:
dump tran db_name to “path/db_backup1.ext” stripe on
“path/db_backup2.ext”
Multiple File with Compression:
dump tran db_name to “path/db_backup1.ext” stripe on
“path/db_backup2.ext” with compression = 5
Now our set up is ready with required types of backup at different frequency. Let’s consider below frequency of backups:
| Type of Backup | Run Time | Frequency |
| Full | 12:30AM | Once |
| Cumulative | 6:40AM, 12:40PM, 6:40PM | Every 6 hours |
| Tran | 15th Minute of 2,4,6,8, 10,12,14,16,18,20,22 | Alternative Hour |
Now, for getting data up to 12:30AM, we can load full dump taken as of 12:30AM. In case, we need data as of 6:40PM, we can use full dump as of 12:30AM and cumulative backup taken as of 6:40PM, earlier taken cumulative backup at 6:40AM and 12:40PM is not required.
Now, lets consider we need data upto 8:15PM. we shall take Full dump followed by cumulative backup at 6:40PM and Tran backup taken at 20:15 and then post online database command.
The most typical requirement would be to get data upto 9:20PM, lets see the set of required command to do so with compress and stripe command as below:
use master
go
load database db_name from “path/db_backup1.ext” stripe on /* Full Backup */
“path/db_backup2.ext”
go
load database db_name cumulative from “path/db_backup1.ext” stripe on /* cumulative backup taken at 6:40PM */
“path/db_backup2.ext”
go
load tran db_name from “path/db_backup1.ext” stripe on /* Tran backup taken at 8:20PM */
“path/db_backup2.ext”
go
load tran db_name from “path/db_backup1.ext” stripe on /* Tran backup taken at 10:20PM */
“path/db_backup2.ext” with until_time = “Oct 21 2021 09:20:00PM”
go
online database db_name
go
Above command will bring restored database upto 9:20PM. I have also explain this in my video SAP Sybase ASE DB Restoration From Different Backup Types
Please leave your comment if you liked this post or have any feedback..