Backup and Restore
A database backup enables you to go back and restore a database to a point in time. Doing so protects you from data issues and/or corruptions that you may have had since the last backup. In addition, you can restore the database on different hardware in case the current hardware has undergone a complete and unrecoverable system crash. A database restore will cause an outage that will take time to perform, how long depending on the data size and speed of your system.
The design of your backup strategy starts with an analysis of the risk of an outage and the amount of time you can afford the system to be unavailable. In addition, consider the cost of the backup solution itself.
Vector supports live, incremental backups if you enable journaling on the first full read-only backup. The backup is always a database-level backup.
The following sections describe various backup strategies. In some cases, it makes sense to use a combination of these approaches for optimum high availability.
Database Backup Utilities (ckpdb and rollforwarddb)
The ckpdb utility can be used to take the backup, and rollforwarddb to restore and recover the database.
The initial backup must be a full backup with no outstanding transactions. To take live incremental backups after the initial backup you must turn on journaling. Upon restore you can replay incremental backups to get back to the most recent incremental backup. You can take up to 99 incremental backups following a full backup but of course the database restore and recovery takes longer as more incremental backups have to be applied. You should plan to periodically take a full backup to reset the counter for incremental backups.
For information on how to use the ckpdb and rollforwarddb utilities, see the Vector User Guide.
The backup file is not compressed by default, but you can pipe the output into a compression utility to compress the backup file.
Full Operating System Database Backup
A full Operating System backup takes a backup of the database files. The advantage of a full backup is that it is self-contained and simple to understand. Disadvantages to an OS backup are the amount of storage space required to store the backup, the amount of time to take the backup, and the lack of options to restore only a subset of the database.
To back up the database:
1. Stop the applications accessing the database.
2. Shut down the Vector instance.
3. Copy the database files and configuration files to a safe place using tar, cpio, or another utility.
4. Start up the Vector instance.
5. Resume the applications accessing the database.
To restore the database:
1. Stop the applications accessing the database.
2. Shut down the Vector instance.
3. Copy the database file and configuration files from the safe place using tar, cpio, or another utility.
4. Start up the Vector instance.
5. Resume the applications accessing the database.
If the server has completely crashed and cannot be recovered, then you may have to reinstall the Vector software before you can restore the database.
copydb/unloaddb Database Backup
A copydb or unloaddb command will create scripts with database object definitions and commands to unload the database or individual tables. The advantage of this approach is that it is self-contained and simple to understand. In addition, you can back up a subset of the database. Disadvantages of this backup mechanism are the amount of storage space required to store the resulting data files and the time to perform the unload and reload of the database.
To back up the database:
• Stop the applications accessing the database.
• Run copydb or unloaddb and the associated scripts on the database.
• Resume the applications accessing the database.
To restore the database:
• Stop the applications accessing the database.
• Either drop the database objects or destroy/create the database.
• Run the scripts to reload the database.
• Resume the applications accessing the database.
For more information, see copydb and unloaddb in the Vector User Guide.
Partial Database Backup
If you want the option of a partial database restore, then you must take a partial database backup. For example, if you need to perform data maintenance on a table and you want to make sure that you can revert to the data set prior to the change, then you should take a backup of the table’s data set. Use CREATE TABLE AS SELECT to store a copy of the data in a different table or use the COPY statement to export the data from the table into a file. To restore the table, use MODIFY...TO TRUNCATED or the MODIFY...TO COMBINE command to delete all the data from the table:
MODIFY basetable TO TRUNCATED
Then, use INSERT AS SELECT FROM to reinsert the data, or the COPY statement to read the data from the file back into the table.
Warning! If you drop the table rather than use MODIFY...TO TRUNCATED or the MODIFY...TO COMBINE statement, any dependent objects will be affected--for example, views, grants, constraints.
Database Reload
Your backup strategy may be to reload the database from the data sources. Such a scenario requires that the source data sets are still available for your entire database.
Hybrid Approach: Full Backup with Incremental Reload
To take a full database backup is the easiest way to back up your Vector database. However, your database may be terabytes in size and as a result, depending on the hardware you use, taking and restoring the backup may take a long time. A full database backup will also take up a significant amount of storage space.
As an alternative to the use of a full followed by incremental backup, data warehouses commonly use a strategy of a regular (such as weekly or monthly) full database backup and store incremental data loads (data files) until the next full backup. A database restore and recovery consists of restoring the most recent full database backup followed by a replay of the data loads since the most recent full backup.