Restoring database not all rows restored

Question

I have a table EMPLOYEE.EMPLOYEE inside database HELLO which contains 3 records as listed below:

EMP_NO  BIRTH_DATE FIRST_NAME         LAST_NAME            GENDER HIRE_DATE  BANK_ACCOUNT_NUMBER PHONE_NUMBER  
------- ---------- ------------------ -------------------- ------ ---------- ------------------- --------------
     1. 06/05/1998 A                  B                     M     01/02/2019 026201521420        +91X 
     2. 10/14/1997 C                  D                     M     01/07/2019 034212323454        +91Y 
     3. 05/27/1997 E                  F                     F     01/14/2019 92329323123         +91Z

Then I first take an offline backup using the following commands

mkdir offlinebackup
db2 terminate
db2 deactivate database HELLO
db2 backup database HELLO to ~/offlinebackup/

After which I get this output:

Backup successful. The timestamp for this backup image is : 20190128115210

Now I take an online backup using the following commands

db2 update database configuration for HELLO using LOGARCHMETH1 'DISK:/database/config/db2inst1/onlinebackup'
db2 backup database HELLO online to /database/config/db2inst1/onlinebackup compress include logs

After this I get the output as:

Backup successful. The timestamp for this backup image is : 20190128115616

Now I go back to db2 and run CONNECT TO HELLO which connects me to my database. When I check for rows in the EMPLOYEE.EMPLOYEE table, I still get all my 3 rows.

Now I remove the row with EMP_NO 3. This gets succesfully removed. Then I run quit from the db2 terminal

Then I use this command to run the restore from my offline backup:

db2 restore db HELLO from ~/offlinebackup/ replace existing

It says DB20000I The RESTORE DATABASE command completed successfully

Now I try to connect to HELLO, it says SQL1117N A connection to or activation of database "HELLO" cannot be made because of ROLL-FORWARD PENDING. SQLSTATE=57019

To which I run db2 rollforward db HELLO to end of logs and stop

Then I connect to HELLO and try to find out the rows, I get only 2 rows, and not 3 as it was in the backup.

EMP_NO  BIRTH_DATE FIRST_NAME         LAST_NAME             GENDER HIRE_DATE  BANK_ACCOUNT_NUMBER PHONE_NUMBER  
------- ---------- ------------------ --------------------- ------ ---------- ------------------- --------------
     1. 06/05/1998 A                  B                     M      01/02/2019 026201521420        +91X 
     2. 10/14/1997 C                  D                     M      01/07/2019 034212323454        +91Y

The third record is not visible, which was present in the backup. Can anyone figure out why I am not able to restore the third record from the backup

Redo the backup and rollforward to a time before you deleted that row. — data_henrik, Jan 28 '19 at 12:25
You can't get a database in `Rollforward Pending State` if you restore into recoverable database from a backup taken from this database when it had circular logging. Such a restored database must be in a `Backup Pending State` instead. Seems that you restored from an online archive taken after switching to the archive logging mode. — Mark Barinstein, Jan 28 '19 at 13:14
Use command "rollforward db HELLO to end of backup" instead of "rollforward db HELLO to end of logs" — Satya, Jan 28 '19 at 13:54

mustaccio · Accepted Answer · 2019-01-28T19:28:11.433

The rollforward command that you ran:

db2 rollforward db HELLO to end of logs and stop

replayed all available logs, including the record corresponding to the delete statement.

If you wanted to restore the database to the state right after the backup was taken you could have run

db2 rollforward db HELLO to end of backup and stop

Alternatively, since you are restoring from an offline backup, rollforward is altogether not necessary and you could have used

db2 rollforward db HELLO stop

Alternatively, skip the rollforward completely (for offline backups only, of course):

db2 restore db HELLO from ~/offlinebackup/ replace existing without rolling forward

Thanks. This worked. The first command worked for online backup restore and both of them for offline backup restore. — Sparker0i, Jan 28 '19 at 19:25

Restoring database not all rows restored

1 Answers1