DB2 database Backup and Recovery experiment script

--Offline Backup database testing

--Create a database
DB2 Create DB TestDB

--Create a test table, insert Test records, and verify
DB2 Connect to TestDB
DB2 CREATE TABLE T1 (C1 int,c2 VarChar (50))
DB2 INSERT INTO T1 (C1,C2) VALUES (1, ' Offline backup test ')
DB2 SELECT * FROM T1

--Backup Database (describes the type of database backup)
DB2 Backup DB TestDB to D:\DB2_Train

--Simulate disaster, delete database
DB2 Drop DB TestDB

--Recover the database and verify
DB2 Restore DB TestDB from D:\DB2_Train

DB2 Connect to TestDB
DB2 SELECT * FROM T1

---------------------------------------------
--Online backup database testing

--Change Database configuration parameters
DB2 Update DB CFG using Logretain on

--Offline full backup of the database, leaving the database out of the "Backup pending" state
DB2 Backup DB TestDB to D:\DB2_Train

--Insert a new record in the test table and verify
DB2 INSERT INTO T1 (C1,C2) VALUES (2, ' online backup test ')
DB2 SELECT * FROM T1

--Online backup Database
DB2 Backup DB TestDB online to D:\DB2_Train

--Get the database activity log path and back up the log

--Simulate disaster, delete database
DB2 Drop DB TestDB

--Recover Database
DB2 Restore DB TestDB from D:\DB2_Train taken at 20100426075221

--Connect to the database (will fail)
C:\>DB2 Connect to TestDB
SQL1117N cannot connect or activate the database because it is in the Roll-forward PENDING state
"TESTDB". sqlstate=57019

--roll forward the database to make it available
DB2 Rollforward DATABASE TESTDB to END of LOGS and complete OVERFLOW LOG PATH ("D:\DB2_Train\SQLOGDIR")

--Connect to the database and test
DB2 Connect to TESTDB

DB2 SELECT * FROM T1

---------------------------------------------------------
--Incremental backup database testing

--Change database parameters to support incremental backups
DB2 Update DB CFG using Trackmod on

--You must back up the database completely offline before performing an incremental backup
DB2 Backup DB TestDB to D:\DB2_Train

--Insert a new record in the test table and verify
DB2 Connect to TestDB
DB2 INSERT INTO T1 (C1,C2) VALUES (3, ' incremental backup test ')
DB2 SELECT * FROM T1

--Perform an incremental backup command
DB2 Backup DB TestDB incremental to D:\DB2_Train

--Simulate disaster, delete database (note BACKUP log)
DB2 Drop DB TestDB

--Method 1: Restore the database manually based on the incremental backup file
DB2 Restore DB TestDB incremental from D:\DB2_Train taken at 20100426080406

C:\>DB2 Connect to TestDB
SQL1119N cannot connect or activate the database "TESTDB" because the previous recovery is incomplete or is still in progress.
sqlstate=57019

DB2 Restore DB TestDB incremental from D:\DB2_Train taken at 20100426080303

C:\>DB2 Connect to TestDB
SQL1119N cannot connect or activate the database "TESTDB" because the previous recovery is incomplete or is still in progress.
sqlstate=57019

C:\>DB2 Restore DB TestDB incremental from D:\DB2_Train taken at 20100426080406
The db20000i RESTORE DATABASE command completed successfully.

C:\>DB2 Connect to TestDB
SQL1117N cannot connect or activate the database because it is in the Roll-forward PENDING state
"TESTDB". sqlstate=57019

--roll forward the database to make it available

DB2 Rollforward DB BILL_BT to end of logs and stop overflow log path "(C:\DB2LOG_BILL_BT)" or
DB2 Rollforward DATABASE TESTDB to END of LOGS and complete OVERFLOW LOG PATH ("D:\DB2_Train\SQLOGDIR")

--Test
DB2 Connect to TestDB
DB2 SELECT * FROM T1

--Method 2: Automatically restore the database based on an incremental backup file
DB2 Drop DB TestDB

DB2 Restore DB TestDB incremental automatic from D:\DB2_Train taken at 20100426080406

--roll forward the database to make it available
DB2 Rollforward DATABASE TESTDB to END of LOGS and complete OVERFLOW LOG PATH ("D:\DB2_Train\SQLOGDIR")

--Test
DB2 Connect to TestDB
DB2 SELECT * FROM T1

----------------------------------------------------------------------------------------------------
--Table Space REDIRECT Recovery test for database

--Create a test table space and record its ID (4)
DB2 CREATE REGULAR tablespace data_tb PAGESIZE 4 K MANAGED by DATABASE USING (FILE ' D:\DB2\NODE0000\TESTDB\DATA_TB ' 2560 ) Bufferpool IBMDEFAULTBP

DB2 List tablespaces Show Detail
DB2 list tablespace containers for 4 show detail

--Create a Test table 2 on the table space, insert a new record in the test table, and verify
DB2 CREATE TABLE T2 (C1 int,c2 VarChar) in DATA_TB
DB2 INSERT INTO T2 (C1,C2) VALUES (4, ' redirect Backup Test ')
DB2 SELECT * FROM T2

--Now you want to transfer the tablespace DATA_TB of the database to the path D:\DB2_Train\TestDB_TableSpace Unified Storage

--first step, back up the database, then simulate the disaster, delete the database
DB2 Backup DB TestDB to D:\DB2_Train

DB2 Drop DB TestDB

--Step Two, edit the script, prepare to redirect the recovery database
--Tablespace 0,1,2,3 is a system-managed automatic memory table space and does not require or allow changes to the storage location
DB2 Restore DB TestDB from D:\DB2_Train taken at 20100426082206 redirect
DB2 set tablespace containers for 4 using (file ' D:\DB2_Train\TestDB_TableSpace\0 ' 2560)
DB2 Restore DB TestDB continue

--roll forward the database to make it available
DB2 Rollforward DB TestDB to end of logs and complete
DB2 Connect to TestDB
DB2 SELECT * FROM T2

1. Get all instances under the system

Db2ilist;

2. Setting the current instance

Set db2instance=db2fuel\

3. Get the current instance

DB2 Get Instance

4 display all databases under the system

DB2 List DB Directory

5 Deleting a database

DB2 Drop DB dbname

6 Recovering a database backup file

DB2 RESTORE DATABASE Bill from D:\ Taken at 20100528174528 to E:into bill_bt logtarget c:\bill_log_bt newlogpath C:\BILL_LOG_BT

DB2 Restore DB BILL_BT continue

DB2 Rollforward DB BILL_BT to end of logs and stop overflow log path "(C:\DB2LOG_BILL_BT)"

DB2 Connect to BILL_BT

DB2 List tablespaces