FirebirdSQL logo
  • Anglaiskeyboard_arrow_down

Locking/Unlocking database in case of Virtual Machines Backup

Using Virtual Machines backup tools without preparing database for such type of backup can lead to the corrupted (i.e. useless) backup copy.

Firebird server intensively uses its own cache in RAM to speed up operations, and implements complex techniques to ensure database consistency at the every given moment.Virtual Machine backup tools are not aware about Firebird’s cache, and usually they do not consider database files as random-access files.

As a result, when the virtual machine backup is done, the database file inside it will have the state as after a hard reset of VM, and very often such copy is not consistent (i.e. corrupted).The chance of such problem is higher when many active users are changing the database, or if there is active sweep process.

Such inconsistent backups can occur in any virtualized environment, including public clouds.

In order to create good Firebird database backup with VM backup tool, it is necessary to lock database file with nbackup before the VM backup, and unlock after it.Usually VM backup tool allows executing custom pre- and post-backup scripts, where you can lock/unlock Firebird databases.

Setting the delta file

By default, the delta file lives in the same directory as the database itself.The file name is also the same, but with .delta appended.This is usually not a problem, but sometimes it is desirable or even necessary to change the location, e.g. when the database is stored on a raw device.Nbackup itself has no provision for setting the location;this must be done through SQL.

Make a connection to the database with any client that allows you to enter your own SQL statements and give the command:

alter database add difference file 'path-and-filename'

The custom delta file specification is persistent in the database;it is stored in the system table RDB$FILES.To revert to the default behaviour, issue the following statement:

alter database drop difference file

You can also specify a custom delta location while creating a new database:

create database 'path-and-dbname' difference file 'path-and-deltaname'
Note

  • If you specify a bare file name with [ADD] DIFFERENCE FILE, the delta will likely not be created in the same directory as the database, but in the current directory as seen from the server.On Windows this may be the system directory.The same logic applies to relative paths.

  • The entire directory path must already exist.Firebird doesn’t attempt to create any missing directories.

  • If you want to change your custom delta specification, you must first DROP the old one and then ADD the new one.

docnext count = 3

Backup history

The firebird database keeps a history of all nbackup activity in the system table RDB$BACKUP_HISTORY.This information is used by nbackup itself for internal housekeeping, but can also be used to find out when the last backup was done, on which level and what the filename is.

For example, to see the last 5` backups 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 5

The columns of RDB$BACKUP_HISTORY are:

Column Description

RDB$BACKUP_ID

Primary key

RDB$TIMESTAMP

Time and date of backup

RDB$BACKUP_LEVEL

Backup level

RDB$GUID

GUID of the backup (used to check dependencies between files)

RDB$SCN

Highest page marker in the backup

RDB$FILE_NAME

Filename of the backup

For an explanation of the field RDB$SCN see the section [nbackup-background].

The contents of the table RDB$BACKUP_HISTORY are not backed up and restored by gbak;see the section [nbackup-background] for details.

Technical background information

Nbackup performs a physical backup of the database pages by copying pages that have been modified since the last backup of the immediately preceding level.A level 0 backup copies all pages, while a level 1 copies only those pages that have been modified after the most recent level 0.To be able to find the modified pages, Firebird uses a marker that is called the SCN (short for page scan).This number is incremented at each backup state change.For each backup with nbackup there are three state changes:

  1. nbak_state_normal (no backup) to nbak_state_stalled (database writes to delta file)

  2. nbak_state_stalled to nbak_state_merge (merging delta file back into database)

  3. nbak_state_merge to nbak_state_normal (no backup)

Note

These three state changes occur even if the backup fails.

The SCN of the database before the start of the backup is recorded together with the backup.The very first backup gets SCN 0, the second 3, etc.This number is independent of the level of the backup.The SCN is used to mark the pages of a database.So for example:

SCN Explanation

0

Pages before any backup

1

Pages written/updated into the delta file during the backup

2

Pages written/updated during the merge of delta file into main backup

3

Pages written/updated after ending first backup+merge

When a level 1 backup is made, nbackup looks for the last level 0 backup and backs up all pages with an SCN higher than the SCN of that level 0 backup (and so on).

A backup and restore with gbak does not restore the content of the RDB$BACKUP_HISTORY table, and it resets 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.

Document history

The exact file history is recorded in the firebird-documentation git repository; see https://github.com/FirebirdSQL/firebird-documentation

Revision History

0.1

21 Oct 2005

PV

First edition.

1.0

1 Dec 2006

PV

  • Removed “beta” reference in edition info.Changed warning against specifying backup file names interactively with nbackup -R.Removed “(or will be)” from first sentence in Document History.

  • Changed C:\Databases to C:\Data in the examples, just to keep the lines from running out of the shaded screen areas in the PDF.

  • Added section Setting the delta file, and changed section Read on? accordingly.

1.1

5 May 2008

PV

  • Making and restoring backups: Added warning about heavy-load risks with nbackup 2.0.0–2.0.3.

  • Restoring a full backup: Corrected wrong statement that nbackup will overwrite an existing database if there are no active connections.Changed italic text about interactive restore failure to a Note and mentioned its fix in 2.0.1.

  • Incremental backups: Inserted warning that incremental backups are broken in 2.1.

  • Suppressing database triggers (Firebird 2.1+): New section.

  • Read on?: Fixed typo (you → your).

1.2

19 Sep 2011

PV

  • Document source formatting: Changed max. line length to 100, without open lines.

  • All sections and subsections now have an id.

  • Introduction: Edited first sentence.

  • Nbackup features — an overview: First sentence: groups → kinds.Edited last para before first subsection: mentioned that only SYSDBA, owner and sometimes OS admins can make a backup.

  • Nbackup features — an overview :: Limitations of nbackup: Edited previously last listitem to mention Services Manager.Added listitem about direct file access.Removed last para.

  • Functions and parameters: New section.

  • Making and restoring backups: Slightly altered last sentence of first para.Extended warning: added info on the role of direct I/O with large databases under Posix.

 

 

 

  • Making and restoring backups :: Full backups :: Making full backups: Corrected and extended listitem on -U and -P parameters.Added listitems on -FE parameter (new in 2.5), -T parameter (new in 2.1) and -D parameter (new in 2.5, backport to 2.1.4).In listitem starting with “The different parameters”, the parenthesized text now reads (-B, -U etc.), because many new parameters have been added.

  • Making and restoring backups :: A word on the inner workings: Small edit (image → impression).

  • Making and restoring backups :: Full backups :: Restoring a full backup: Removed parameters -U and -P from specification.Added listitem on aliases.Changed separate Note about interactive restore failure back to italic text inside the listitem itself.Added listitem about non-necessity of running server and ignoring credentials.

  • Making and restoring backups :: Incremental backups: Edited Warning: mentioned fix in 2.1.1.

  • Making and restoring backups :: Incremental backups :: Restoring incremental backups: Removed parameters -U and -P from formal syntax and 1st listitem.

  • Making and restoring backups :: Backing up raw-device databases: New section.

  • Making and restoring backups :: Suppressing database triggers: Edited and extended this section, but removed the “SYSDBA and owner only” remark.

  • Making and restoring backups :: Direct I/O (Firebird 2.1.4+): New section.

  • Making and restoring backups :: Informational options (Firebird 2.5+): New section.

  • Making and restoring backups :: Backups on remote machines (Firebird 2.5+): New section.

  • Locking and unlocking: Slightly altered last sentence of second para.

  • Locking and unlocking :: Locking the database and backing up yourself: Added Comments (para + itemizedlist).

  • Locking and unlocking :: Restoring a backup made after nbackup -L: Added info on use of alias and -Z to step 2 of procedure.In next para, translated “en” (leftover from Dutch original) → “and”.Added sentence to Note about reading -F as Flag-only.

  • Locking and unlocking :: Locking raw-device databases: New section.

  • Locking and unlocking :: Under the hood: Edited Note.

  • Setting the delta file: 1st para largely rewritten;now refers to raw-device databases.Split off last sentence into a para of its own.Added info (para + programlisting) about setting delta with CREATE DATABASE.1st listitem in Notes: ADD → [ADD].

 

 

 

  • Document history: Changed ulink to CVS (both text and url);now points directly to document.

  • License notice: End year in copyright mention now 2011.

1.3

12 Oct 2011

PV

  • Functions and parameters: In first table: self-restored → user-restored.In second table: self-restore → user restore.

  • Locking and unlocking :: Locking the database and backing up yourself: Section renamed Locking the database and making the backup yourself.

  • Locking and unlocking :: Restoring a backup made after nbackup -L: 2nd listitem in 1st itemizedlist: self-restored → user-restored.

1.4

18 Sep 2014

MR

  • Backup history: New section

  • Technical background information New section

1.5

27 Jun 2020

MR

Conversion to AsciiDoc, minor copy-editing

1.6

25 Jul 2020

AK

Paragraph about VM backups, example with non-standard port, performance notice paragraph on incremental backup

1.7

10 Oct 2022

MR

  • Added switches introduced in Firebird 3.0 and 4.0

  • Various copy-editing

  • Fix rendering of Document History in PDF by breaking long entries into multiple entries