Takes a backup copy of a databank file.
This SQL statement is used to take a backup of a databank.
A backup is a copy of the current databank file and may be used as the basis for a databank recovery operation, see ALTER DATABANK RESTORE.
The backup will be recorded in a file on disk, the name of the file is specified in the CREATE BACKUP statement.
In order to preserve the consistency of the backup between related databanks, a backup of each of the databanks must be taken at exactly the same point in time, from the point of view of transactions updating the databanks. This is done by starting a transaction for the online backup operations using the START BACKUP statement, then executing a CREATE BACKUP statement for each databank to be backed up. Finally conclude the transaction by executing the COMMIT BACKUP statement or ROLLBACK BACKUP statement.
It is recommended that all databanks (including system databanks) in a database are backed up together in this way.
The CREATE BACKUP command creates the backup file. The actual copying of data from the databank to the backup file is not done until a COMMIT BACKUP is executed.
When the keyword EXCLUSIVE is used, the backup of the databank will be taken without allowing any concurrent operations. Otherwise, the backup will be taken online, i.e. other operations can be executed concurrently.
When a backup of LOGDB is taken, changes made on all databanks are copied to the backup. I.e. this corresponds to taking an incremental backup of all databanks. The entire log is dropped when the backup transaction is committed.
When LOGDB is not included in the backup, only the information that applies to the backed up databanks is dropped from the database log. Note that, in this case, it will not be possible to restore the databanks from a previous backup, as the log records are not saved. Therefore, it is highly recommended to always include LOGDB whenever any databank is backed up.
CREATE BACKUP requires that the current ident be the creator of the databank or have BACKUP privilege.
The CREATE BACKUP statement cannot be executed unless a transaction, that was started by executing a START BACKUP statement, is currently active.
A backup requires read access to all tables in the databank. It is therefore not possible to take a backup when commands, such as ALTER TABLE and CREATE INDEX, are executing. When a backup has been initiated, commands that require exclusive access will get an error indicating the table is in use by another user.
The value of filename-string must always be enclosed in string delimiters.
The maximum length of filename-string is 256 characters.
Refer to Specifying the Location of User Databanks for details concerning specification of the path name components in filename-string.
The CREATE BACKUP command can be used with all databanks in a database including SYSDB, TRANSDB, LOGDB and SQLDB.
The databank option will affect the backup copy:
A consistent backup is made of the databank. Transaction logging is used and it will be possible to redo operations made after the backup.
A consistent backup is made of the databank. But as transaction logging is not used, it will not be possible to redo operations made after the backup. I.e. if a disk is corrupted, it is only possible to revert to the state of the latest backup.
An online backup of the databank will give a backup which is not completely consistent as the system uses the transaction system to make backups. For a completely consistent backup to be made, the keyword EXCLUSIVE must be used in the CREATE BACKUP command.
For read only databanks the backup is always consistent.
The removal of records from the database log to maintain consistency with the backups is handled automatically by these statements, i.e. no additional commands are needed.
The following example starts a backup transaction, creates backup files for the specified databank files, commits the backup and exits:
CREATE BACKUP IN 'user_databank' FOR DATABANK user_databank;
CREATE BACKUP IN 'logdb_backup' FOR DATABANK logdb;
CREATE BACKUP IN 'sysdb_backup' FOR DATABANK sysdb;
CREATE BACKUP IN 'transdb_backup' FOR DATABANK transdb;
For more information, see the System Management Manual, Backing-up and Restoring Data.
Mimer SQL extension
The CREATE BACKUP statement is a Mimer SQL extension.