Browse DevX
Sign up for e-mail newsletters from DevX


Defy Murphy's Law with DB2 Backup and Restore-3 : Page 3




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Offline or Online Backup?
The syntax used in the previous section is that of an offline backup, which assumes that no user or process is connected to the database except for the one performing the backup. Keep the list applications command in mind. It lists the processes that are currently accessing the database to which you are connected.

In a production environment, you might not have the liberty to tell your database users (or processes that access your database) to go away so you can perform a database backup. In such a case, consider an online backup. Online backups require only that the tablespaces being backed up not have a user (other than the one performing the backup) connected to them.

Before you can perform an online backup, you have to make sure that the roll-forward recovery option is enabled for your database. This is not turned on by default. To enable this option, use the following command:

db2 => update database configuration for backdem using logretain on

In order for the configuration change above to take effect, you must stop and restart DB2. You can do so by issuing the commands db2stop and db2start from the command line. Also, at least one full offline database backup must be performed after the logretain option has been enabled and before an online database backup can take place. Assuming that all these requirements have been met, you can use the following command to back up the userspace1 tablespace in which your table (secretidentities) is housed:

db2 => backup database backdem tablespace(userspace1) online to c:\dbback

Again, you will be provided with a timestamp for your backup image when the backup is completed.

Restore Your Database from Backups
How do you restore your backed-up database? Similar to backups, you have offline and online restore options. The following command performs an offline restore that requires exclusive access to the entire database:

db2 => restore database backdem user db2admin using db2admin from
C:\dbback taken at 20031026195337 without rolling forward

In the syntax above, you effectively destroy whatever data resides in backdem and replace it with your backup image. Also, make note of the without rolling forward clause. It states that you do not want transactions that occurred after your backup to be recovered. (The next section discusses how to recover these unaccounted-for transactions.)

In the following online restoration syntax, exclusive access is required only for the tablespace: userspace1:

db2 => restore database backdem tablespace (userspace1) online from
c:\dbback taken at 20031026195337 without rolling forward

A very important feature to note is that you don't have to restore to the database from which you backed up. You can create a new database and restore your backup to the new database. This is extremely helpful when you have a number of programming team members who each needs access to his or her individual, yet similar database structure. The following syntax shows how to backup to a new database, which you named newdb:

db2 => restore database backdem from c:\dbback taken at 20031026195337 into newdb

The DB2 Control Center provides a graphical interface for restoring your database. You can access it by choosing the "Restore" option from the context menu of the database of interest (as Figure 3 shows).

Click to enlarge
Figure 3: Restoring from the Control Center

The resulting Restore Data Wizard walks you through the restoration process from a database image (see Figure 4).

Click to enlarge
Figure 4: The Restore Wizard

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date