Print this page.  If your browser doesn't allow JavaScript, right-click this page and choose Print from the popup-menu.        
Mimer SQL Online Backup
Categories: Database administration, Database features
Introduction

When using Mimer SQL, you can backup all databanks, including the system databanks, while the system is operational. You initiate and execute the backup using SQL statements. These statements are also described in the Mimer SQL System Management Handbook found in the Mimer SQL Documentation Set.

Function

You can use the following SQL statements for managing an online backup:

  • START BACKUP. Starts a so-called “backup transaction”
  • CREATE BACKUP. Creates a backup within a backup trans-action. By default, an online backup is created, but optionally an exclusive backup can be initiated, which will lock the databank from other users.
  • COMMIT BACKUP. Commits a started backup transaction. The databank files are copied and synchronized and the log records for the databank backups taken are deleted.
  • ROLLBACK BACKUP. Aborts a backup transaction and ensures that all log records are preserved. The backup files are deleted.


To use these statements to take a databank backup, you must either be the creator of the databank, or have the BACKUP privilege.

By taking a backup of all the databank files, it will be easier to restore a corrupted environment. If you instead take an online backup for the LOGDB databank only, it is equivalent to an incremental backup of all databanks.

Including the LOGDB databank in the backup set drops the entire log file.

An online backup in progress may slow down system performance slightly; therefore we recommend that you schedule the operation to start at a point when the system workload is as low as possible.

Another way of avoiding performance loss is to put the largest databank first in the CREATE BACKUP list.

As the backup transaction uses several parallel threads and asynchronous I/O, it is a good idea, if possible, to place backup files on separate disks, using separate disk controllers.

Please note that it is recommendable to run the databank check functionality (the DBC program) before archiving the backup databank files (e.g. copying them to a removable device). DBC verifies the physical integrity of the databank files.

Please note that databanks with WORK option are copied as consistent as if the system crashed.

Techniques

Using a backup transaction ensures that the backups taken within the transaction are consistent with one another, as each backup is effectively taken at the same point in time. Log records are cleared for successfully copied databanks when the backup transaction is committed. If the LOGDB system databank is included in the backup transaction, all log records are cleared.

A CREATE BACKUP statement is executed for each databank to be backed up. You can ensure consistency between the databanks by starting a backup transaction using the START BACKUP statement.

You commit the backup transaction by using the COMMIT BACKUP statement, which will perform the backup and clear the relevant LOGDB records. To abort a backup transaction you can execute the ROLLBACK BACKUP statement, this will preserve LOGDB.

To backup databanks online, do the following:

1. Enter the following SQL statements to initiate and execute the backup.


SQL> START BACKUP;
SQL> CREATE BACKUP IN 'backup-file-name' FOR DATABANK databank-name;
.
. (repeat the CREATE statement 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;

2. Verify the backup copies from the operating system command line using the DBC program.

dbc backup-filename report-filename sysdb-filename
.
. (repeat the dbc command for each backup file created above)
.
.

3. Archive the verified backup copies (e.g. copy to a removable device).

Benefits

The Mimer SQL online backup functionality provides a method for backup management that is easy to use.

The main advantage of online backup is that the database server can continue to operate (backup operations are performed in the background).

The backup files are compressed and reorganized.

A disadvantage can be that there must be enough disk space available to copy the complete database.

Links

The article How to Backup Mimer SQL Online.

The manual Mimer SQL Documentation Set, PDF-file (see the SQL Reference Manual and the System Management Handbook).

The manual Mimer SQL Documentation Set, html navigation (see the SQL Reference Manual and the System Management Handbook).


Last updated: 2009-01-19

 

Powered by Mimer SQL

Powered by Mimer SQL