DB2 Backup restores detailed tests.

Test environment for this article: Windows XP, IBM DB2 Express V9.5, DB2 Quest Center V9.5

Here is a scenario to illustrate the idea of a database recovery backup:

So let's say we did a backup of the database in the evening of Monday, and the database storage media failed at 12 o'clock noon in Tuesday. How can recovery be achieved? So the first thing is to restore the database to the moment we backed up (Monday night), but what about the transactions (things) that have been done to the database since the backup and Tuesday 12 o'clock. This requires a database log, because once the transaction is committed, the SQL (INSERT, UPDATE, Delete, and so on) that we make to the database is recorded in the database log. So we use the database log (provided that the database log is not corrupted) and redo all SQL operations (Redo) after the backup and before the database crashes. This is the principle of database backup recovery.

1, DB2 Data Backup and recovery experiment preparation work

STEP1: Creating a Test database TestDB

1 CREATE DATABASE TestDB
2 on ' E: '
3 USING codeset GBK TERRITORY CN
4 with ' DEMO TestDB ';

Results:

SCRIPT Start:connection:lenovo-milo-db2-toolsdb (db2admin) jul-09-2009 14:34:49 * * *

CREATE DATABASE TestDB
On ' E: '
USING codeset GBK TERRITORY CN
With ' DEMO TestDB ';
Completed successfully.
db20000i CREATE Database command completed successfully.

Statement processed successfully in 32.42 secs.

SCRIPT END:Connection:NONE jul-09-2009 14:35:21 * * *



STEP2: Create a database management table interval DATA_SP (to set up paths and files first)

The path here is: E:/db2_train/testdb/userdata
File name is set to: Testdb.userdata


1 CREATE REGULAR tablespace data_sp
2
3 PAGESIZE 4K
4
5 MANAGED by DATABASE
6
7 USING (FILE ' E:/db2_train/testdb/userdata/testdb.userdata ' 2560)
8
9 Bufferpool IBMDEFAULTBP;

This sets the table space initialized to 4K * 2560 = 10MB

Result: tablespace A table space called data_sp

STEP3: Creating a Test table TestTable
1 CREATE TABLE TestTable
2 (
3 ID INTEGER not NULL GENERATED all as IDENTITY (START with 0, INCREMENT by 1, NO CACHE),
4 Message VARCHAR (100),
5 PRIMARY KEY (ID)
6) in data_sp;

Results:
:

STEP4: Insert test Data under testtable 1 insert INTO testtable (Message)
2 VALUES (' Test table build Success ')

--Description: Build the test table and insert the data in order to verify the database recovery later

Results:


Or you can use
1 Db2stop Force
2 Db2start
3 DB2 Connect to TestDB
4 DB2 SELECT * FROM TestTable

STEP5:DB2 Database offline backup and Recovery experiment

Description: I placed the files that were backed up offline in the following path: E:/db2_train/offline

1. Fully offline BACKUP DATABASE 1 db2stop Force//stop and start DB2 instance
2 Db2start
3
4 DB2 Connect to TestDB
5//Specifies that the currently active database is TestDB
6
7 DB2 Backup DB TestDB to "E:/db2_train/offline"
8//Full backup database, (offline, backup timestamp is 20090709152004)
9
10

2. Simulate disaster scene, force delete TestDB database 1 DB2 drop DB TestDB

3. Restore database based on full database backup
1//Recover database with offline full backup
2 DB2 Restore DB TestDB from ' E:/db2_train/offline ' taken at 20090709152004

4, inquiry form testtable inside content, this piece to focus on, because it involves three kinds of backup way difference
1 Db2stop Force
2 Db2start
3
4 DB2 Connect to TestDB
5
6 DB2 SELECT * FROM TestTable

STEP6, DB2 database incremental backup and recovery experiment

1. View database configuration file 1//Query the status of parameter Trackmod in database configuration file, default is off
2 DB2 get DB CFG for TestDB

2, modify the database configuration parameters trackmod, make the database incremental backup 1//Set parameter trackmod to Yes, so that the database can be incremental backup
2 DB2 update db CFG for TestDB USING trackmod YES

3. After changing the parameters, full offline full backup of the database must be required 1