DB2: Comandos Backup e Restore

Comando Backup

db2 "BACKUP DATABASE MYDB"

This will generate a file like MYDB.0.db2inst1.DBPART000.20240605211641.001

Error Msg: SQL1035N The operation failed ... SQLSTATE=57019

1. Check Active Connections

db2 list applications for database BLUDB

2. Force Disconnect All Users

db2 force applications all
db2 terminate

3. Deactivate Database

db2 deactivate database BLUDB

4. Perform Backup

db2 "BACKUP DATABASE BLUDB ONLINE TO /mnt/backup COMPRESS"

for offline remove "ONLINE" parameter

Comando Restore

db2 "CREATE DATABASE MYDB AUTOMATIC STORAGE YES USING CODESET UTF-8 TERRITORY US 
    COLLATE USING IDENTITY PAGESIZE 4096 DFT_EXTENT_SZ 32"

db2 "RESTORE DATABASE MYDB from /tmp into MYDB without prompting"

Check backup file

 db2ckbkp -H /tmp/

=====================
MEDIA HEADER REACHED:
=====================
Server Database Name           -- MYDB
Server Database Alias          -- MYDB
Client Database Alias          -- MYDB
Timestamp                      -- 20240605211641
Database Partition Number      -- 0
Instance                       -- db2inst1
Database Configuration Type    -- 0 (Non-shared data)
Sequence Number                -- 1
Database Member ID             -- 0
Release ID                     -- 0x1500 (DB2 v11.5.9.0)
AL version                     -- V:11 R:5 M:9 F:0 I:0 SB:0
...
The proper image file name would be:
MYDB.0.db2inst1.DBPART000.20240605211641.001

Restore using redirect file

You can use the "redirected" restore option by generating a file, using command:

db2 restore database MYDB redirect generate script restore.sql

Once the script is generated, you can change the names, file locations, etc.

And finally, execute the script

db2 -tvf restore.sql

Ver também