FirebirdSQL logo
  • Anglaiskeyboard_arrow_down
  • Search

Locking the database and making the backup yourself

A typical session in which you make your own backup goes as follows:

  1. Lock the database with the -L (lock) switch:

    nbackup [-U user -P password] -L database

  2. Now copy/backup/zip the database file to your heart’s content, with your own choice of tools. A simple file copy is also possible.

  3. Unlock the database with -N (uNlock), or — since Firebird 3.0 — -UN:

    nbackup [-U user -P password] -N database

The last command will also cause any mutations — which have been written to the delta file — to be merged into the main file.

The backup you made contains the data as they were at the moment the database was locked, regardless how long the locked state has lasted, and regardless how long you may have waited before making the actual backup.

Comments:

  • Instead of a database filename you may also specify an alias.

  • The -U and -P parameters may be omitted if the envars ISC_USER and ISC_PASSWORD are set, if you are root on a Posix system, or if trusted authentication under Windows permits it.For a detailed description see the comments under Making full backups.

  • Starting with Firebird 2.5, instead of -P password you may also use -FE filename.

  • Both -L and -N (-UN) make a regular connection to the database, so in Firebird 2.1 and above it may be wise to add the -T (or -NODBTRIGGERS) parameter (see Suppressing database triggers).

  • If you’re locking a raw-device database with Firebird 2.1 or above, the -S option can be very helpful;see [nbackup-lock-unlock-rawdevices].

  • You can optionally add -Z to have version information printed on the first line of the output.

Warning

What goes for backup/restore also applies to the lock/unlock switches: do not use them on multi-file databases.Until things have changed, don’t let nbackup loose on multi-file databases at all!

Restoring a backup made after “nbackup -L”

A copy of a locked database is itself a locked database too, so you can’t just copy it back and start using it.Should your original database get lost or damaged and the self-made copy needs to be restored (or should you wish to install the copy on another machine), proceed like this:

  1. Copy/restore/unzip the backed-up database file yourself with the necessary tools.

  2. Now unlock the database, not with the -N (or -UN) switch, but with -F (fixup):

    nbackup -F database

    Here too, you can optionally use an alias instead of a filename, and add -Z for version info.Other options make no sense.

Why are there two unlock switches, -N/-UN and -F?

  • -N/-UN first sees that any changes made since the locking by -L are merged into the main database file.After that, the database goes back into normal read/write mode and the temporary file is deleted.

  • -F only changes the state flag of the user-restored database to “normal”.

So you use:

  • -N/-UN after having made a copy/backup yourself (to reverse the -L issued earlier);

  • -F after having restored such a backup yourself.

Note

The term fixup is used because it doesn’t attempt to reintegrate the delta-file, but only makes the minimum changes necessary to make the database file usable again.

docnext count = 7

Under the hood

Note

This section doesn’t contain any necessary knowledge, but provides some extra information which could deepen your understanding of the various switches.

nbackup [parameter] -L does the following:

  1. Connect to the database;

  2. Start a transaction;

  3. Call ALTER DATABASE BEGIN BACKUP (this statement has been discussed in the extra information on nbackup -B);

  4. Commit the transaction;

  5. Disconnect from the database.

nbackup [parameter] -N follows the same steps, but with “…​ END BACKUP” in step 3.

nbackup [parameter] -F works as follows:

  1. The restored database file is opened;

  2. Within the file, the state flag is changed from locked (nbak_state_stalled) to normal (nbak_state_normal);

  3. The file is closed again.

Note

nbackup -F operates purely on file level and can therefore also be performed without a Firebird server running.Any credentials supplied via the -U, -P or -FE parameters are ignored, just as with nbackup -R.

Locking raw-device databases

As discussed in [nbackup-backups-rawdevices], problems can arise if a delta has to be created for a database located on a raw device.Therefore, in Firebird 2.1 and up, nbackup refuses to operate on raw-device databases unless an explicit location for the delta file has been set previously.For the procedure, see [nbackup-deltafile], a little further down.

There’s also another problem if you lock and copy a raw device: you don’t know the actual size of the database!The raw device may be 10 GB, but the database might only take up 200 MB of that space.To prevent having to copy the entire device just to be on the safe side — possibly wasting huge amounts of time and space — Firebird 2.1 has introduced a new parameter for nbackup: -S.This parameter is only valid in combination with -L and when it is present, nbackup writes the database size in pages to stdout after locking the database.Because the size is given in pages, it has to be multiplied by the database page size in order to get the actual number of bytes to be copied.Or, if you use the dd copy utility, you could specify the page size as (i)bs and the output of nbackup -L -S as count.

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.

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