-- SteveLothspeich - 16 Nov 2006

Database Backup and Restore proceedures

Overview

Obviously the core of a disaster recovery plan is a solid set of backup and restore proceedures. Over the years we have refined our backup policies to the point that they are currently completely automated executed by shell scripts called by cron jobs on various servers. These scripts have all been checked into CVS and should run from any server inside the LAN, or with a little modification, from any server outside the LAN as well. Essentially we run weekly full system backups on Sunday evenings at 22:00 Pacific time. Currently the script is executed from the server niobe and takes approximately 40-45 minutes. This requires us to stop all application servers in order to drop all connections to the database and allow a full database backup.

Along with the weekly full system backups, we also run nightly incremental backups. Through the testing of our backup and restore proceedures we learned that it is imperative to either retain log-files with these incremental backups, or include them with the backup using the 'include logs' option. For consistency and ease of maintenance, we use the 'include logs' options in our incremental backups. Database log files are not required in full system backups, but they are for 'online' backups otherwise you cannot pull a database out of a 'Roll Forward Pending' state. Below are the backup and restore options we are currently using, and which have been tested to work in the event of complete hardware failure.


Backup

Overall the backup commands are very straight forward. Weekly full system backups require all connections to the specific database to be closed to ensure no transactions take place during the process. Once all connections to the specified database have been closed (verified with a db2 list active databases command), we use:

db2 "backup database ds01 to /opt/realtime/backup/db_backups/"
db2 "backup database prod02 to /opt/realtime/backup/db_backups/"
db2 "backup database prod01 to /opt/realtime/backup/db_backups/"

As you can see, the statements above backup the DS01, PROD02 and PROD01 databases and store the backup file in /opt/realtime/backup/db_backups/. Once backed up, we zip the files and transfer them to seperate servers for redundancy and tape archival.

Nightly incremental backups do not require stopping of the application servers since they are considered online backups and only grab data since the last full system backup. Our incremental backups do not require any previous incremental backups, but they do require the previous full system backup and the active database log file at the time of the incremental backup. For these backups, we use:

db2 "backup database ds01 online incremental to /opt/realtime/backup/db_backups/incremental/ include logs"
db2 "backup database prod02 online incremental to /opt/realtime/backup/db_backups/incremental/ include logs"
db2 "backup database prod01 online incremental to /opt/realtime/backup/db_backups/incremental/ include logs"

The statements above are currently run at approximately 23:00 Pacific time Monday through Friday. As you can see, they are set to backup the same databases as the weekly full backups but as one would guess, they are much smaller in size since we are only grabbing the changed info and the current log file. Without the include logs option, it isn't possible to get the database out of a Roll Forward Pending state unless we have a copy of the log file in use at the time of the backup. As you can tell, it is very important to have this option enabled.


Restore

There are multiple options when restoring a database. To name a few, we can:

  • Restore to a new server for testing or to recover some data which may have been accidentally removed.

  • Restore to a different database name (similar to restoring on a different server, but instead we use the same server and a different database name and we don't affect the current database, but we can still look up data that may have been accidentally removed).

  • Restore over the current database (usually only used in a serious or catastrophic situations since all previous data will be overwritten).

Note: Any time you are restoring a database, it is highly advised that you backup the log files in the database SQLOGDIR for the particular database so logs aren't accidentally overwritten, otherwise it is possible to lose data which those logs retain. To do this, find out where the specific database log directory is located and tar the most recent log files (a few before and everything after the backup image timestamp).

To restore just the latest full system backup, you can create a new directory to copy the latest backup file into (I usually create a restore subdirectory under the db_backups directory, but any empty directory will do. Copy the latest full system backup to the new directory, and from this directory, use:

db2 restore database [database name] replace existing
db2 rollforward database [database name] to end of logs and stop
db2 rollforward database [database name] stop
This will restore the database over the top of the previous version which again should only be used in an emergency or if the database has become corrupt. Don't forget to tar the log files before doing this or you may regret it!

You can restore a full database into a new database name for testing or to recover a few lost records by using:

db2 restore database [database name] into [new database name]
db2 rollforward database [new database name] to end of logs and stop
db2 rollforward database [new database name] stop

This will be the most likely use of the Restoring data in the event of a catastrophic failure
If you are planning on using an incremental restore, the steps change slightly. We first run an restore with the incremental option of the latest incremental backup. The data from this file isn't actually inserted yet, the file is just read as a baseline. Then we restore the last full system backup followed by each subsequent incremental backup (if delta backups are used) or in our case, just followed by the latest incremental backup.

  1. Using tar, backup the database log files so no additional data is accidentally overwritten or lost.

  1. db2 restore database sample incremental taken at [ts]
    where: [ts] points to the last incremental backup image (the target image)

  1. db2 restore database sample incremental taken at [ts1]
    where: [ts1] points to the initial full database (or table space) image

  1. db2 restore database sample incremental taken at [tsX]
    where: [tsX] points to each incremental backup image in creation sequence

  1. Repeat Step 3, restoring each incremental backup image up to and including image

  1. Recover the log file associated with the latest incremental backup
    db2 restore database sample logs taken at [ts] logtarget [location of db log directory]

EXAMPLE:
In the restore directory we have the two following backup files:

PROD02.0.realtime.NODE0000.CATN0000.20061112220147.001
PROD02.0.realtime.NODE0000.CATN0000.20061116100559.001
And we use db2 restore database prod02 incremental taken at 20061116100559
db2 restore database prod02 incremental taken at 20061112220147
db2 restore database prod02 incremental taken at 20061116100559
db2 restore database prod02 logs taken at 20061116100559 logtarget /opt/realtime/realtime/NODE0000/SQL00003/SQLOGDIR/
db2 rollforward db prod02 to end of logs and stop
db2 rollforward db prod02 stop

Again, if the log file is not restored from the last incremental backup, it is not possible to pull the database out of a 'Roll Forward Pending' state and it cannot be accessed. If for some reason it isn't possible to get the log file from the incremental backup, you should be able to use the latest log file on the database if it is accessible. The incremental files we currently create should all have the log file packaged with them so this shouldn't ever be a problem.

As a confirmation, the above incremental restore process was tested on the PROD02 database on November 16, 2006 and I have verified that not only can we access the database, but records added after the full system backup taken on 11/12/2006 were available. This was tested on a seperate machine with a clean version of IBM DB2 version 8.2.


smile ------------
Topic revision: r2 - 2016-03-31 - StephenNolan
 
This site is powered by the TWiki collaboration platform Powered by PerlCopyright © 2008-2024 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback