CREATE BACKUP

---

Takes a backup copy of a databank file.

Usage

Embedded/Interactive/ODBC/JDBC.

Description

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.

Restrictions

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.

Notes

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:

• LOG

  A consistent backup is made of the databank. Transaction logging is used and it will be possible to redo operations made after the backup.

• TRANSACTION

  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.

• WORK

  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.

• READ ONLY

  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.

Example

The following example starts a backup transaction, creates backup files for the specified databank files, commits the backup and exits:

 START BACKUP;
    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;
    CREATE BACKUP IN 'sqldb_backup' FOR DATABANK sqldb;
 COMMIT BACKUP;
 EXIT;
 

For more information, see the Mimer SQL System Management Handbook, Backing-up and Restoring Data.

Standard Compliance

Standard	Compliance	Comments
	Mimer SQL extension	The CREATE BACKUP statement is a Mimer SQL extension.

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se