DB2 online backup restore to different database

[tsaol...@gmail.com]

I tried to use DB2 online backup sample to restore to database tsample2, restore is OK, but when I tried to rollforward with different methods, all failed, anyone knows?

Here are my output

C:\Program Files\IBM\SQLLIB\BIN>db2 backup database sample user ABC using 'Password' online to c:\DB2\backup\sample\
Backup successful. The timestamp for this backup image is : 20160708173836


C:\Program Files\IBM\SQLLIB\BIN>db2 create database tsample2 automatic storage yes on c:
DB20000I The CREATE DATABASE command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2 restore database sample user ABC using 'Password' from c:\DB2\backup\sample\ taken at 20160708173836 into tsample2 redirect generate script c:\DB2\backup\restore_to_tsample2.txt
DB20000I The RESTORE DATABASE command completed successfully.


c:\DB2\backup>db2 -tvf restore_to_tsample2.txt
UPDATE COMMAND OPTIONS USING S ON Z ON SAMPLE_NODE0000.out V ON
DB20000I The UPDATE COMMAND OPTIONS command completed successfully.

SET CLIENT ATTACH_MEMBER 0
DB20000I The SET CLIENT command completed successfully.

SET CLIENT CONNECT_MEMBER 0
DB20000I The SET CLIENT command completed successfully.

RESTORE DATABASE SAMPLE USER ABC USING FROM 'c:\DB2\backup\sample\' TAKEN AT 20160708173836 INTO TSAMPLE
2 REDIRECT
SQL2529W Warning! Restoring to an existing database that is different from
the backup image database, and the alias name "TSAMPLE2" of the existing
database does not match the alias name "SAMPLE" of the backup image, and the
database name "TSAMPLE2" of the existing database does not match the database
name "SAMPLE" of the backup image. The target database will be overwritten by
the backup version. The Roll-forward recovery logs associated with the target
database will be deleted.
Do you want to continue ? (y/n) y
SQL1277W A redirected restore operation is being performed. During a table
space restore, only table spaces being restored can have their paths
reconfigured. During a database restore, storage group storage paths and DMS
table space containers can be reconfigured.
DB20000I The RESTORE DATABASE command completed successfully.

RESTORE DATABASE SAMPLE CONTINUE
DB20000I The RESTORE DATABASE command completed successfully.


c:\DB2\backup>db2 connect to tsample2
SQL1117N A connection to or activation of database "TSAMPLE2" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019

c:\DB2\backup>db2 rollforward db tsample2 to end of backup and complete
SQL4970N Rollforward recovery stopped on the database named "TSAMPLE2"
because the rollforward utility cannot reach the specified stop point
(end-of-log or point-in-time) on the following database partitions: "0".

c:\DB2\backup>db2 rollforward db tsample2 to end of logs and complete
SQL4970N Rollforward recovery stopped on the database named "TSAMPLE2"
because the rollforward utility cannot reach the specified stop point
(end-of-log or point-in-time) on the following database partitions: "0".

c:\DB2\backup>db2 rollforward db tsample2 complete
SQL1276N Database "TSAMPLE2" cannot be brought out of rollforward pending
state until roll-forward has passed a point in time greater than or equal to
"2016-07-08-09.38.37.000000 UTC", because node "0" contains information later
than the specified time.

c:\DB2\backup>db2 rollforward db tsample2 to 2016-07-08-09.38.37 and complete
SQL1274N The database "TSAMPLE2" requires roll-forward recovery and the
point-in-time must be to the end of logs.

c:\DB2\backup>db2 rollforward db tsample2 to 2016-07-08-09.38.37 and stop overflow log path (c:\DB2\db2data\tsample2)
SQL1274N The database "TSAMPLE2" requires roll-forward recovery and the
point-in-time must be to the end of logs.

[Jeremy Rickard]

Hi,

You could use db2diag to get a better idea of what DB2 is trying to do that doesn't work.

Once you've specified end of logs you cannot switch to a point-in-time instead.

A basic question - have you copied the necessary transaction logs into the log path or overflow log path?

This should all be simple, something like:

1) Repeat the restore (replace existing)
2) rollforward db tsample2 query status
3) Copy all required transaction logs to an overflow path
4) rollforward db tsample2 to end of logs overflow log path (<your path>)
5) rollforward db tsample2 complete overflow log path (<your path>)


Regards, Jeremy

[ox1d0]

Maybe you will need to take the backup using include logs then use restore with logtarget option this will extract the needed logs into some path where you can use overflow path at the rollforward...


Regards

[jba...@calculo-sa.es]

I think INCLUDE LOGS is the default for version 10.5

[Lance Lau]

Thank you for your tips. It works !!!