Backing-up Databanks

Backing-up Databanks

This section describes procedures for taking databank backups and for restoring a databank in the event of it being damaged or destroyed.

Online Backups Using the SQL Statements

The procedure for taking databank backups using the SQL system management statements is detailed below.

A CREATE BACKUP statement is executed for each databank to be backed up and databank consistency is ensured by starting a backup transaction using the START BACKUP statement.

The backup transaction is committed by using the COMMIT BACKUP statement, which will perform the backup and clear the relevant LOGDB records. The ROLLBACK BACKUP statement can be executed to abort the backup transaction, which will preserve LOGDB.

Note: The databank check functionality (the DBC program) should be run before archiving the backup copies of the databank files (e.g. copying them to CD/RW) to verify the physical integrity of the databank files.

To backup databanks online, do the following:

Perform SQL statements for initiating and executing the backup.
 SQL> START BACKUP;
 SQL> CREATE BACKUP IN 'backup-file-name' FOR DATABANK databank-name;
  .
  .   (repeat for each databank to be backed up)
  .
 SQL> CREATE BACKUP IN 'backup-file-name' FOR DATABANK logdb;
 SQL> CREATE BACKUP IN 'backup-file-name' FOR DATABANK sysdb;
 SQL> CREATE BACKUP IN 'backup-file-name' FOR DATABANK transdb;
 SQL> CREATE BACKUP IN 'backup-file-name' FOR DATABANK sqldb;
 SQL> COMMIT BACKUP;
 SQL> EXIT;
 
Verify the backup copies from the operating system command line using the DBC program.
 dbc  backup-file-name  report-filename  sysdb-file-name
 .
 . (repeat for each backup file created above)
 .
Archive the verified backup copies (e.g. copy to DVD/CD).

The START BACKUP statement will start a backup transaction which will ensure that all the backups taken are consistent with one another (they are effectively backed up at the same point in time).

The CREATE BACKUP statement will only create an empty backup copy file. The entire contents of the specified databank file is copied to the specified file by COMMIT BACKUP. (If any of the backups fail, the ROLLBACK BACKUP statement can be executed to ensure that the log records are preserved.)

The COMMIT BACKUP statement will clear all the LOGDB records that apply to the databanks backed up in the backup transaction.

Databank backup file names are subject to the same restrictions that apply to the SQL statement CREATE DATABANK - see the Mimer SQL Reference Manual.

System Backups Using the Host File System

The procedure for taking databank backups using the host file system is detailed below.

We recommend that you always take a backup of all databanks, including SYSDB, LOGDB, SQLDB and TRANSDB.

Note: The database server must be stopped in order to close the SYSDB databank file for a host system backup. This unlocks SYSDB and ensures that no operations are performed between taking copies of the databanks and dropping the log.
However, if using shadowing, databank shadows allow a copy of a databank to be temporarily set offline, e.g. to be backed up, without interrupting normal system use.

To backup databanks using the system backup method:

Set the database offline using the following command:
 SQL> SET DATABASE OFFLINE;
 
Stop the database server so that the system databanks are closed and can therefore be backed up
 mimcontrol -t database
 
Run the DBC program on each databank to verify the physical integrity of the databank files
 dbc  backup-filename  report-filename  sysdb-filename
 
Perform the backup, e.g. copy all databank files to tape (including the system databanks SYSDB, LOGDB, TRANSDB and SQLDB).
Start the database server
 mimcontrol -s database
 
Set the database online again using the following command to clear all log records:
 SQL> SET DATABASE ONLINE RESET LOG;
 
The RESET LOG option removes all records written to LOGDB since the last backup.
This is essential to maintain consistency between the log and the backup. If the backup fails, the PRESERVE LOG option should be used when setting the databank online to leave LOGDB unaltered.

It is essential that all databanks are backed up at the same time to ensure logical consistency between them.

It is also important that transactions are in a consistent state which is ensured by using the SET DATABASE OFFLINE statement. The statement will not return until the database has been brought into a consistent state prior to going offline. In particular, setting the database offline will ensure all background processing done by the database server has completed.

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