Is it possible to know the level from the last incremental backup, made with nbackup from Firebird?

Question

It's a pretty straightforward question. Firebird has a backup tool called nbackup whereas you can make incremental backups.

The tool complains if you don't make the backup in the correct order (e.g. you make a level 0 backup, then a level 2). Then, I'm assuming that the tool puts a flag in the database indicating the level of the last backup made.

Documentation is provided here

How can I retrieve this information?

Culminate

Answer 1

As far as I know the only way is to query the table RDB$BACKUP_HISTORY (there is no service manager call for this). To get the last backup you can use:

SELECT RDB$BACKUP_ID, RDB$TIMESTAMP, RDB$BACKUP_LEVEL, RDB$GUID, RDB$SCN, RDB$FILE_NAME
FROM RDB$BACKUP_HISTORY
ORDER BY RDB$TIMESTAMP DESC
ROWS 1

The table RDB$BACKUP_HISTORY has the following columns:

• RDB$BACKUP_ID primary key
• RDB$TIMESTAMP timestamp of backup
• RDB$BACKUP_LEVEL back up level
• RDB$GUID guid of the backup (this is also used in the backup files to control and check dependencies)
• RDB$SCN highest page marker in backup (see below)
• RDB$FILE_NAME filename of the created backup

Nbackup does a physical backup of the database pages. The SCN (short for page scan...) is a number that is used to mark database pages. This number is incremented at each backup state change, for each backup with nbackup there are 3 state changes: nbak_state_normal (no backup) -> nbak_state_stalled (database writes to delta file) -> nbak_state_merge (merging delta file back into database) -> nbak_state_normal (no backup).

The first backup gets SCN 0, the second SCN 3, etc (doesn't matter which level).

• SCN 0: Pages before any backup
• SCN 1: Pages written/updated into the delta file during the backup
• SCN 2: Pages written/updated during the merge of delta file into main backup (although I am unsure if the pages from the delta file written back into the main file get SCN 1 or 2)
• SCN 3: Pages written/updated after ending first backup+merge
• ...
• SCN 6: Pages written/updated after ending second backup+merge

When you make a level 1 backup, it looks for the last level 0 backup and backs up all pages with a SCN higher than the SCN of that level 0 backup (and so on). This also described in the Firebird 2.1 release notes: New On-line Incremental Backup.

Note that a backup and restore with gbak will clear the RDB$BACKUP_HISTORY table and reset the SCN of all pages back to 0. The reason for this is that gbak creates a logical backup instead of a physical backup. So a restore using gbak will rewrite the entire database (and can even change the page size). This renders previous backups with nbackup meaningless as a starting point for subsequent backups: you need to start with a fresh level 0.

As this information is missing in the nbackup manual, I have created a ticket on the Firebird tracker: http://tracker.firebirdsql.org/browse/DOC-94

Consalve

Exactly what I was looking for and more. Thank you! I hope this help lots of people. – Culminate