CUBRID Backup & Restore – Part II (Restore)
The scope of this tutorial is to present the database backup & restore capabilities in CUBRID. This is the 2nd part of the tutorial, focusing on CUBRID Restore.
A database restore is the procedure of restoring the database to a previous state, by using the backup files, active logs and archive logs created by a database backup procedure.
To perform a database restore, you will use the cubrid restoredb utility or the CUBRID Manager client.
CUBRID restoredb utility
The cubrid restoredb utility restores the database from a database backup, by using the information written to all active and archive logs, since the execution of the last backup.
>cubrid restoredb [ options ] database_name [ options ] -d | -B | -l | -p | -o | -u | --up-to-date | --backup-file-path | --level | --partial-recovery | --output-file | --replication-mode | --use-database-location-path | --list
For a successful restore, you must prepare backup files, active log files and archive log files. This command can be performed only in standalone mode.
The following table shows options that can be used with cubrid restoredb. Note that the options are case sensitive.
|Sets the time back when to restore the database or specifies the backuptime keyword.|
|Specifies the folder or the device name where backup files are located.|
|Sets the recovery level to 0, 1 or 2. The default value is a full recovery (0).|
|Performs a partial recovery.|
|Specifies the name of the file where recovery information is to be displayed.|
|Recovers the database to the path specified in the database location file (databases.txt).|
|--list||Displays information about backup volumes of the database on the screen.|
In this section we will present various examples of using the restoredb utility.
Performing a recovery by specifying a recovery point (-d or --up-to-date)
The following command recovers demodb. If no option is specified, demodb is recovered to the point of the last commit by default. If no active/archive log files are required to recover to the point of the last commit, the database is recovered only to the point of the last backup.
Note: If the -D option is not specified, backup files are stored in the folder specified in the databases.txt file.
>cubrid restoredb demodb
demodb can be recovered to the given point by using the -d option and the syntax which specifies the date and time of the recovery. The user can specify the recovery point manually in the dd-mm-yyyy:hh:mm:ss (e.g. 14-10-2010:14:10:00) format. If no active log/archive log files are required to recover to the point specified, the database is recovered only to the point of the last backup.
>cubrid restoredb -d 14-10-2010:14:10:00 demodb
The following syntax specifies the recovery point by using the -d option and the backuptime keyword and recovers demodb to the point of the last backup.
>cubrid restoredb -d backuptime demodb
Performing a recovery by specifying the directory path to the backup files (-B or --backup-file-path)
You can specify the directory where backup files are to be located by using the -B option. If this option is not specified, the system retrieves the backup information file (dbname_bkvinf) generated upon a database backup; the backup information file in located in the log_path directory specified in the database location information file (databases.txt). And then it searches the backup files in the directory path specified in the backup information file. However, if the backup information file has been damaged or the location information of the backup files has been deleted, the system will not be able to find the backup files. Therefore, the administrator must manually specify the directory where the backup files are located by using the -B option.
>cubrid restoredb -B /home/cubrid/backup demodb
If the backup files of demodb is in the current directory, the administrator can specify the directory where the backup files are located by using the -B option.
>cubrid restoredb -B . demodb
Performing a recovery by specifying the backup level (-l or --level)
You can perform a restoration by specifying the backup level of the database to 0, 1, or 2.
>cubrid restoredb -l 1 demodb
Performing a partial recovery (-p or --partial-recovery)
The following command performs a partial recovery without requesting for the user's response, by using the -p option. If active or archive logs written after the backup point are not complete, by default the system displays a request message informing that log files are needed and prompting the user to enter an execution option. A partial recovery can be performed directly without such a request message by using the -p option. Therefore, if the -p option is used when performing a recovery, data is always recovered to the point of the last backup.
>cubrid restoredb -p demodb
When the -p option is not specified, the message requesting the user is prompted to select the execution option:
*********************************************************** Log Archive /home/cubrid/test/log/demodb_lgar002 is needed to continue normal execution. Type - 0 to quit. - 1 to continue without present archive. (Partial recovery) - 2 to continue after the archive is mounted/loaded. - 3 to continue after changing location/name of archive. ***********************************************************
Storing recovery progress information in the specified file (-o or --output-file)
The following command writes the recovery progress of the database to the info_restore file by using the -o option.
>cubrid restoredb -o info_restore demodb
Recovering data to the directory specified in the database location file (-u or --use-database-location-path)
The following syntax recovers the database to the path specified in the database location file (databases.txt) by using the -u option. The -u option is useful when you perform a backup on server A and recover the backup files on server B.
>cubrid restoredb -u demodb
Checking the backup information of the database (--list)
The following syntax displays the information about backup files of the database by using the --list option; it does not perform recovery.
>cubrid restoredb --list demodb
The following is an example of backup information displayed as a result of using the --list option. You can identify the path to which backup files of the database are originally stored as well as backup levels:
*** BACKUP HEADER INFORMATION *** Database Name: /local1/testing/demodb DB Creation Time: Mon Oct 1 17:27:40 2010 Pagesize: 4096 Backup Level: 1 (INCREMENTAL LEVEL 1) Start_lsa: 513|3688 Last_lsa: 513|3688 Backup Time: Mon Oct 1 17:32:50 2010 Backup Unit Num: 0 Release: 8.1.0 Disk Version: 8 Backup Pagesize: 4096 Zip Method: 0 (NONE) Zip Level: 0 (NONE) Previous Backup level: 0 Time: Mon Oct 1 17:31:40 2010 (start_lsa was -1|-1) Database Volume name: /local1/testing/demodb_vinf Volume Identifier: -5, Size: 308 bytes (1 pages) Database Volume name: /local1/testing/demodb Volume Identifier: 0, Size: 2048000 bytes (500 pages) Database Volume name: /local1/testing/demodb_lginf Volume Identifier: -4, Size: 165 bytes (1 pages) Database Volume name: /local1/testing/demodb_bkvinf Volume Identifier: -3, Size: 132 bytes (1 pages)
With the backup information displayed by using the --list option, you can check that backup files have been created at the backup level 1, as well as the point where the full backup of backup level 0 has been performed. To recover the database in the example, you must prepare first backup files for backup levels 0 and 1.
Backup & Restore on different servers
The following section shows how to backup demodb on server A and restore it on server B.
Let’s suppose that demodb is backed up in the /home/cubrid/db/demodb folder on server A and we want to restore it into /home/cubrid/data/demodb on server B.
Here are operations we need to perform:
Backing up on server A
Back up demodb on server A. If a backup has been performed earlier, you can perform an incremental backup for data only that have changed since the last backup. The directory where the backup files are created, if not specified in the -D option, is created by default in the location where the log volume is stored. The following is a backup command with recommended options:
>cubrid backupdb -z -t demodb
Editing the database location file on Server B
Unlike a general scenario where a backup and restore are performed on the same server, in a scenario where backup files are restored using a different server, you need to add the location information on database restore in the database location file (databases.txt) on server B. In the diagram above, it is supposed that demodb is restored in the /home/cubrid/data/demodb directory on server B (having hostname=pmlinux); edit the location information file accordingly and create the directory on server B.
Write the database location information in one single line. Separate each item with a space. The line should be written in [database name]. [data volume path] [host name] [log volume path] format:
demodb /home/cubrid/data/demodb pmlinux /home/cubrid/data/demodb
Transferring backup/log files to server B
For a restore, you must prepare a backup file (e.g. demodb_bk0v000) and a backup information file (e.g. demodb_bkvinf) of the database to be backed up. To restore the entire data up to the point of the last commit, you must prepare an active log (e.g. demodb_lgat) and an archive log (e.g. demodb_lgar000). Then, transfer the backup information, active log, and archive log files created on server A to server B - the backup information, active log and archive log files must be located in a directory (e.g. /home/cubrid/temp) on server B.
Restoring the database on server B
Perform database restore by calling the cubrid restoredb utility from the folder where the backup, backup information, active log and archive log files which were transferred to server B have been stored. With the -u option, demodb is restored in the directory path from the databases.txt file:
>cubrid restoredb -u demodb
To call the cubrid restoredb utility from a different path, specify the directory path to the backup file by using the -B option as follows:
>cubrid restoredb -u -B /home/cubrid/temp demodb
Backing up the restored database on server B
Once the restore of the target database is complete, run the database to check if it has been properly restored. It is also recommended to restore the database again on the server B environment, for safety reasons.
Restore Database from CUBRID Manager Client
To restore a database, perform one of the followings (after login to the database):
- Click Restore Database from the toolbar.
- Right-click the database and then select Restore Database.
- Select Action -> Restore Database on the menu.
Loading data to restore database can be performed only when the database server is not running; The Restore Database menu is disabled while the database is running.
These are the options you can configure:
|Database name||The name of the database to be restored.|
|Restored Date and Time||Specifies to which point in time the database is to be restored back to. If you select Backup time, a restore is performed with the backuptime keyword in the restore utility. This means that the database is restored to the point when the backup was complete. If you select Specify a restore date, you can enter the date and time you want.|
|Available Backup Information||You can select the restore level after checking which levels of backing up have been performed on the target database. The specified file path is the path to the folder where the files for the backup for the selected level are located.|
|Perform partial restore if any log archive is absent||Performs a partial restore if the cases of incomplete log information. A database restore can be performed even without archive or active logs created after the backup point.|
|Restore to a path specified by the user||Restores the database to the path specified in the database location file (databases.txt).|
|Show backup information||Shows the information of the file backed up to the selected backup level.|
If you click Show backup information, you will see information associated with the backup selected:
The followings must be considered before planning and executing a database restore:
- Preparing a backup file
- Identify the directory where the backup and log files are to be stored.
- If the database has been incrementally backed up, check whether an appropriate backup file for each backup level exists.
- Check whether the backed-up CUBRID database and the CUBRID database to be backed up are the same version.
- Choosing a restore method
- Determine whether to perform a partial or full restore.
- Determine whether or not to perform a restore using incremental backup files.
- Prepare restore tools and devices available.
- Determining restore time
- Identify the point in time when the database server was stopped.
- Identify the point in time when the last backup was performed before database failure.
- Identify the point in time when the last commit was made before database failure.
The following is an example of a backup and restore process, described chronologically:
- Performs a full backup of demodb, which stopped running at 2010/8/14 04:30.
- Performs the first incremental backup of demodb running at 2010/8/14 10:00.
- Performs the first incremental backup of demodb running at 2010/8/14 15:00. Overwrites the first incremental backup file in step 2.
- A system failure occurs at 2010/8/14 15:30, and the system administrator prepares the restore of demodb. Sets the restore time as 15:25, which is the time when the last commit was made before the database failure.
- The system administrator prepares the full backup file created in Step 1 and the first incremental backup file created in Step 3, restores the demodb database up to the 15:00 time, and then prepares the active and archive logs to restore the database up to 15:25.
|2010/8/14 04:25||cubrid server stop demodb||Shuts down demodb.|
|2010/8/14 04:30||cubrid backupdb -S -D /home/backup -l 0 demodb||Performs a full backup of demodb in offline mode and creates backup files in the specified directory.|
|2010/8/14 05:00||cubrid server start demodb||Starts demodb.|
|2010/8/14 10:00||cubrid backupdb -C -D /home/backup -l 1 demodb||Performs the first incremental backup of demodb online and creates backup files in the specified directory.|
|2010/8/14 15:00||cubrid backupdb -C -D /home/backup -l 1 demodb||Performs the first incremental backup of demodb online and creates backup files in the specified directory. Overwrites the first incremental backup file created at 10:00.|
|2010/8/14 15:30||A system failure occurs.|
|2010/8/14 15:40||cubrid restoredb -l 1 -d 08/14/2010:15:25:00 demodb||Restores demodb based on the full backup file, first incremental backup file, active logs and archive logs. The database is restored to the point of 15:25 by the full and first incremental backup files, the active and archive logs.|
- CUBRID Backup & Restore – Part I (Backup)
- Online Resource for CUBRID Backup and Restore
- CUBRID Automated Scripts for DB Hosting Service
- CUBRID Replication
- CUBRID Architecture
- Restoring Emergency Database Logs
- CUBRID Tutorials
- CUBRID Manual