db2 backup & restore - liamlamth/blog GitHub Wiki

backup

  • the database should not have existing sessions
  • both full and incremental backup history can be housekept: 
    db2 prune history 20230631 and delete
  • incremental configuration
    --> if any session, may need to restart the db to effect 
    db2 connect to LIAMDB1
db2 update db cfg using TRACKMOD YES immediate
db2 get db cfg show detail| grep TRACKMOD
  • backup 
    db2 list application | grep LIAMDB1
db2 force application (63222)
db2 terminate
db2 deactivate database LIAMDB1
db2 backup database LIAMDB1 incremental to /your/path/ compress

restore

  • the database should not have existing sessions
  • if application is killed, it may reconnect very quickly. issue restore command asap
  • timestamp can be found in the backup filename
  • for incremental backup, check the required full and incremental backup by: 
    db2ckrst -d LIAMDB1 -t 20230701010000
  1. db2 restore database LIAMDB1 from /your/path/ into LIAMDB2 taken at 20230701010000 logtarget /your/path
    db2 restore database LIAMDB1 incremental automatic from /your/path/ into LIAMDB2 taken at 20230701010000 logtarget /your/path redirect generate script LIAMDB1_gs.clp
vi LIAMDB1_gs.clp
db2 -tvf LIAMDB1_gs.clp
  2. copy logtarget file to the archive log directory
    • archive log directory: /db2data/LIAMDB1/db2inst1/LIAMDB1/NODE0000/LOGSTREAM0000/CHG12345
  3. db2 rollforward db LIAMDB2 to end of logs and stop
    • after roll forward, the database will automatically activate (may be after a short time)

  • if database was not dropped/re-created properly (e.g. directly rebuilt lv), may lead to below error
    db2 restore ...

    SQL1005N The database alias "LIAMDB1" already exists in either the local database directory or system database directory. SQLSTATE=00000

    db2 drop database ...

    SQL1031N The database directory cannot be found on the indicated file system. SQLSTATE=58031

    solution:

    db2 uncatalog database LIAMDB1

# the database should be re-created otherwise the db directory will have issue
db2 drop databaseLIAMDB1
⚠️ **GitHub.com Fallback** ⚠️