Thursday, December 11, 2014

Examples on DB2 cold backup

db2 => CONNECT TO SAMPLEDB

   Database Connection Information

Database server        = DB2/NT64 9.7.10
SQL authorization ID   = ADMINIST...
Local database alias   = SAMPLEDB

db2 => QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS
DB20000I  The QUIESCE DATABASE command completed successfully.
db2 => CONNECT RESET
DB20000I  The SQL command completed successfully.
db2 => BACKUP DATABASE SAMPLEDB TO "D:\backup" WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING

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

db2 => CONNECT TO SAMPLEDB

   Database Connection Information

Database server        = DB2/NT64 9.7.10
SQL authorization ID   = ADMINIST...
Local database alias   = SAMPLEDB

db2 => UNQUIESCE DATABASE
DB20000I  The UNQUIESCE DATABASE command completed successfully.
db2 => CONNECT RESET
DB20000I  The SQL command completed successfully.

db2 => list backup all for database sampledb

                    List History File for sampledb

Number of matching file entries = 1


Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20141211074809001   F    D  S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
  Contains 3 tablespace(s):

00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
----------------------------------------------------------------------------
    Comment: DB2 BACKUP SAMPLEDB OFFLINE
Start Time: 20141211074809
   End Time: 20141211074811
     Status: A
----------------------------------------------------------------------------
  EID: 2 Location: D:\backup

Sample Restoration Script:

CONNECT TO SAMPLEDB
QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS
CONNECT RESET
RESTORE DATABASE SAMPLEDB FROM "D:\backup" TAKEN AT 20141211074809 WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING
CONNECT TO SAMPLEDB
UNQUIESCE DATABASE
CONNECT RESET