Technical Blog Post
Abstract
How to do an incremental database restore with rebuild option
Body
Here is an example about how to perform an incremental restore with rebuild option. In the original database, we have 2 tablespaces created, TBSP01 and TBSP02. When doing incremental db restore, we are going to restore only one of them. The other one will not be available in restored database.
db2 create db MYDB01
db2 update db cfg for MYDB01 using logarchmeth1 logretain TRACKMOD on
db2 terminate
db2 backup db MYDB01 to /dev/null
db2 connect to MYDB01
db2 create tablespace tbsp01
db2 create tablespace tbsp02
db2 "create table t01 (id int) in tbsp01"
db2 "create table t02 (id int) in tbsp02"
Then we take a full backup and then an incremental backup.
$ db2 backup db MYDB01 online to .
Backup successful. The timestamp for this backup image is : 20181109165419
$ db2 connect to MYDB01
Database Connection Information
Database server = DB2/LINUXX8664 10.5.8
SQL authorization ID = INST105
Local database alias = MYDB01
$ db2 "insert into t01 values 1,2"
DB20000I The SQL command completed successfully.
$ db2 backup db MYDB01 online incremental to .
Backup successful. The timestamp for this backup image is : 20181109165426
In restoration, we see warning regarding the partial restore. And the rebuild option is only needed in the first restore command.
$ db2 "restore db MYDB01 rebuild with tablespace (syscatspace, tbsp01) incremental from . taken at 20181109165426 into MYDB01 replace existing without prompting"
SQL2540W Restore is successful, however a warning "2561, 2539" was
encountered during Database Restore while processing in No Interrupt mode.
The returned code 2561 and 2539 respectively means:
SQL2561W Warning! Rebuilding a database from a table space image or
using a subset of table spaces. The target database will be
overwritten. The restore utility also reports the following
sqlcode "<sqlcode>".
SQL2539W The specified name of the backup image to restore is the same
as the name of the target database. Restoring to an existing
database that is the same as the backup image database will cause
the current database to be overwritten by the backup version.
$ db2 restore db MYDB01 continue
DB21080E No previous RESTORE DATABASE command with REDIRECT option was issued
for this database alias, or the information about that command is lost.
$ db2 "restore db MYDB01 incremental from . taken at 20181109165419 into MYDB01 replace existing without prompting"
DB20000I The RESTORE DATABASE command completed successfully.
$ db2 "restore db MYDB01 incremental from . taken at 20181109165426 into MYDB01 replace existing without prompting"
DB20000I The RESTORE DATABASE command completed successfully.
$ db2 "rollforward db MYDB01 to end of logs and stop"
SQL1271W Database "MYDB01" is recovered but one or more table spaces are
offline on members or nodes "0".
After the rollfoward finishes with a warning, we can access the database now. But the tablespace which was not included in the restore command will be inaccessible.
$ db2 connect to MYDB01
Database Connection Information
Database server = DB2/LINUXX8664 10.5.8
SQL authorization ID = INST105
Local database alias = MYDB01
$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
T01 INST105 T 2018-11-09-16.54.06.888212
T02 INST105 T 2018-11-09-16.54.07.048859
2 record(s) selected.
$ db2 "select * from t01"
ID
-----------
1
2
2 record(s) selected.
$ db2 "select * from t02"
ID
-----------
SQL0290N Table space access is not allowed. SQLSTATE=55039
UID
ibm11140118