I'm using DB2 v9.1 LUW. I created a simple database and table and inserted some rows. I'm testing the backups and restores. Then did a db2 backup (both online and offline). Then I deleted the rows from the table. Then did a restore and rollforward. Well the table shows 0 rows so it didn't restore the table data. I don't understand why. Could anyone help me with what I'm doing wrong? I thought a backup would restore the data in the database.

Here is the online backup of what I did:

dbtest$ db2 "select * from db2inst1.test"

ID TIMESTAMP
----------- --------------------------
1 2011-02-02-16.52.08.582552
2 2011-02-02-16.53.27.752275

2 record(s) selected.


dbtest$ db2 backup db test online to /db2_backup/dbtest/db
Backup successful. The timestamp for this backup image is : 20110202165610

dbtest$ db2 "delete from db2inst1.test"
DB20000I The SQL command completed successfully.

dbtest$ db2 "select * from db2inst1.test"

ID TIMESTAMP
----------- --------------------------

0 record(s) selected.


dbtest$ db2 restore db test from . taken at 20110202165610 replace existing

SQL2539W Warning! Restoring to an existing database that is the same as the
backup image database. The database files will be deleted.
DB20000I The RESTORE DATABASE command completed successfully.
dbtest$ db2 rollforward db test to end of logs and complete

Rollforward Status

Input database alias = test
Number of nodes have returned status = 1

Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000003.LOG - S0000004.LOG
Last committed transaction = 2011-02-02-22.57.19.000000 UTC

DB20000I The ROLLFORWARD command completed successfully.
dbtest$ db2 connect to test

Database Connection Information

Database server = DB2/AIX64 9.1.4
SQL authorization ID = DB2INST1
Local database alias = TEST

dbtest$ db2 "select * from db2inst1.test"

ID TIMESTAMP
----------- --------------------------

0 record(s) selected.



Then I thought I had to restore the logs and that did not work, I got an SQL2581N error.



dbtest$ db2 restore db test logs from . taken at 20110202165610 logtarget /db2_data/test/logs/NODE0000
SQL2581N Restore is unable to extract log files from the backup image to the
specified path. Reason code "1".
dbtest$ db2 rollforward db test to end of logs and complete

Rollforward Status

Input database alias = test
Number of nodes have returned status = 1

Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000003.LOG - S0000004.LOG
Last committed transaction = 2011-02-02-22.57.19.000000 UTC

DB20000I The ROLLFORWARD command completed successfully.
dbtest$ db2 connect to test

Database Connection Information

Database server = DB2/AIX64 9.1.4
SQL authorization ID = DB2INST1
Local database alias = TEST

dbtest$ db2 "select * from db2inst1.test"

ID TIMESTAMP
----------- --------------------------

0 record(s) selected.

Guess what the ROLLFORWARD .... TO END OF LOGS command does. Consider rolling forward to a point in time, keeping in mind that ROLLFORWARD expects the timestamp value in UTC.

If you rollforward to end of logs, that will include any activity captured in the logs that happened after the backup finished.

You can rollforward to a specific point in time to avoid this, or if you restore an offline backup you can restore without rolling forward.

In DB2 9.7, you can restore to end of backup (not sure if this feature is available in DB2 9.1).

To obtain the minimum rollforward time (for restoring online backups), you can rollforward to a time several years prior, and DB2 will give you an error message telling you the minimum rollforward time (you can resubmit the rollforward again with the corrected time). Check the Command Reference manual for the exact syntax and date format on rollforward.

Another option is to specify the INCLUDE LOGS option when performing an online backup and then extract them from the image and use during rollforward (specify OVERFLOW LOG PATH and NORETRIEVE options)

Okay I got the rollforward with the timestamp value to work, so thank you for that information.

I'm trying to get the option with rollfoward (specify OVERFLOW LOG PATH and NORETRIEVE options) to work. I'm trying different things as tests and documenting.
I did this:

dbtest$ db2 backup db test online to /db2_backup/dbtest/db include logs

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

dbtest$ db2 restore db test from . taken at 20110203120755 replace existing
SQL2539W Warning! Restoring to an existing database that is the same as the
backup image database. The database files will be deleted.
DB20000I The RESTORE DATABASE command completed successfully.


Then I removed the logs:
dbtest$ cd /db2_data/test/logs/NODE0000
dbtest$ ls
S0000000.LOG S0000002.LOG S0000004.LOG S0000006.LOG S0000008.LOG S0000010.LOG S0000012.LOG S0000014.LOG
S0000001.LOG S0000003.LOG S0000005.LOG S0000007.LOG S0000009.LOG S0000011.LOG S0000013.LOG SQLLPATH.TAG
dbtest$ rm *.LOG


Then I restored the logs from the backup:
db2 restore db test logs from . taken at 20110203120755 logtarget /db2_data/test/logs/NODE
DB20000I The RESTORE DATABASE command completed successfully.


Now I'm trying to do a RF as you suggested but I might be doing it wrong too. I'm getting an error. Can you explain this and let me know what I'm going wrong?


db2 rollforward db test to end of logs and stop overflow logs path /db2_data/test/logs/NODE0000 noretrieve

Here is the error:
SQL0104N An unexpected token "logs" was found following "OVERFLOW". Expected
tokens may include: "LOG". SQLSTATE=42601


I want different options on how to restore from the backup or at a specific point in time...

Thank you so much for your help and please be patient with me, I'm not experienced with this...yet

What I usually do is this:

1) backup online, include logs
2) restore db and logs at same time (just add the logtarget clause to the main restore)
3) do rollforward and complete (it will usually fail because it needs to rollforward to a minimum time).
4) rollforward to the minimum time given is step 3

As long as the logs can be read from logtarget during the rollforward, it should work.

Andy

rockycj, going by what is entered:

db2 rollforward db test to end of logs and stop overflow logs path /db2_data/test/logs/NODE0000 noretrieve

The Error message tells you exactly what it doesn't like.

SQL0104N An unexpected token "logs" was found following "OVERFLOW". Expected
tokens may include: "LOG". SQLSTATE=42601

You have LOGS (plural) after OVERFLOW. The syntax requires LOG (singular).

Follow ARWinner's suggestion and you shouldn't have any problem.

One more thing. When you restore log files to logtarget from the backup (if you don't already have the logs needed in the active or archive log paths), then make sure the logtarget path is empty before you start (which means that you should use a separate logtarget path other than active log or archive log path).