Backing up databases before or after upgrade

For recoverable databases, a database upgrade can be a recoverable operation. Depending on your environment and upgrade window, it can be possible to take advantage of this recoverability feature to reduce your overall upgrade time and minimize your downtime.

This can impact your need for taking an offline database backup before and after the database upgrade. You must review Recovering through a DB2 server upgrade to see whether this is acceptable for the databases in your environment.

If your recovery plan for database upgrade will make use of roll forward through database upgrade, then before the upgrade process to Db2 version 11.5, it is suggested that you perform an online database backup to reduce the recovery time in case of failure with the upgrade procedure.

If you have a recent backup image available for use that is sufficient as well. Regardless, verify by using db2ckbkp that a good backup image exists and ensure all log files needed to recover from the identified backup image exist and are valid by using db2cklog. If your recovery plan for database upgrade will make use of roll forward through database upgrade, then after the upgrade process to Db2 version 11.5, it is recommended that you perform a full online database backup at your earliest convenience. A backup post-upgrade reduces the recovery time and simplifies the recovery procedure in case of failures.

If your recovery plan for database upgrade will not make use of roll forward through database recovery, then before and after the upgrade process to Db2 version 11.5, it is recommended that you perform a full offline database backup. If an error occurs during the upgrade process, you need full database backups to recover and upgrade your databases.

Note:

Online database backup images can only be restored to the Db2 Version where they were created. If you intend to restore the backup image to Db2 version 11.5 then it must be an offline database backup image.
After you upgrade your instances to Db2 version 11.5, you cannot back up databases until you upgrade them.
If users want to create incremental backups of the upgraded database, a full database backup is required following the database upgrade. This serves as the new starting point for the incremental backups.

Before you begin

To back up a database, you require SYSADM, SYSCTRL, or SYSMAINT authority.
Databases must be cataloged. To view a list of all the cataloged databases in the current instance, enter the following command as an instance user:
```
   db2 LIST DATABASE DIRECTORY
```

Procedure

To perform a full offline back up for each of your local databases:

For offline backup: Disconnect all applications and users from the database.
To get a list of all database connections for the current instance, issue the LIST APPLICATIONS command:
```
db2 LIST APPLICATIONS
```
If all applications are disconnected, this command returns the following message:
```
SQL1611W No data was returned by the Database System Monitor.
SQLSTATE=00000
```
To disconnect all applications and users, use the FORCE APPLICATION command:
```
db2 FORCE APPLICATION ALL
```
Backup your database using the BACKUP DATABASE command.
In a Db2 pureScale® environment, you can run the BACKUP DATABASE command from any member.
The following is an example for UNIX operating systems:
Offline :
```
db2 BACKUP DATABASE database_alias USER username USING password TO backup-dir
```
Online:
```
db2 BACKUP DATABASE database_alias USER username USING password ONLINE TO backup-dir
```
where database_alias is the database alias, the user name is username, the password is password, and the directory to create back up files is backup-dir.
In partitioned database environments, back up all database partitions.

If you activated and configured Db2 Advanced Copy Services (ACS) on your databases in Db2 version 10.5 or later, you can use the USE SNAPSHOT parameter to perform a snapshot backup. However, you can only restore a snapshot backup to an instance of the same version. You cannot use snapshot backup to upgrade to a new server. For details, see Performing a snapshot backup.

For offline backup: If you performed a full online or offline database backup recently and you cannot perform another one before upgrading, you can perform an incremental offline database backup instead

Optional: Test the integrity of a backup image to ensure that the image can be restored using the db2ckbkp command.

The following command is an example on UNIX operating systems:

   cd backup-dir   
   db2ckbkp SAMPLE.0.arada.NODE0000.CATN0000.20091014114322.001

   [1] Buffers processed:  #######

   Image Verification Complete - successful.

Optional: Check the integrity of the log files needed to recover from the backup image used in step 3. For details, see Checking archive log files with the db2cklog tool.