DB2 Restore DB Command

Personally, I prefer to use Restore DB DB2 Command, instead of that Restore Database Wizard in Control Center. Don't know why, maybe I easily get confused by buttons and drop downs. ;-)

Here I will run down a simulation of backing up and restoring the database under different name in separate DB2 instance resides within same physical machine. Similar scenario might be to replicate databases for development, testing and production.

First, Fire up the Command Window (db2cmd)

Create the Development instance:

db2icrt DEV

Start the DEV instance

SET DB2INSTANCE=DEV
db2start

Create the sample database

CREATE DB DEVDB

Check out the list of tablespaces in the database, this is important when later you need to redirect the creation of tablespace containers.

db2 connect to DEVDB
db2 list tablespaces

Backup the sample DEVDB database. This will create a folder DEVDB.0 under C:

db2 BACKUP DB DEVDB TO C:

Create the Production instance:

db2icrt PROD

Start the PROD instance

SET DB2INSTANCE=PROD
db2start

Restore the backup DEVDB under C: to PRODDB in PROD instance.

db2 restore db DEVDB FROM C: INTO PRODDB redirect
db2 set tablespace containers for 0 using (path 'C:\container\tspace00c1')
db2 set tablespace containers for 1 using (path 'C:\container\tspace01c1')
db2 set tablespace containers for 2 using (path 'C:\container\tspace02c1')
db2 restore db DEVDB continue

Try to connect to the new PRODDB restored from DEVDB

db2 connect to PRODDB

The trick here is to specify a separate set of container paths for Restore DB command. This will depends on number of tablespaces used by your DB. Default is only 3 tablespaces: SYS, TEMP and USER.


To clean up the simulation, do the following:

set DB2INSTANCE=DEV
db2stop force
set DB2INSTANCE=PROD
db2stop force

db2idrop DEV
db2idrop PROD

Manually remove C:\DEV, C:\PROD, C:\DEVDB.0 and C:\container