Backup Restore

aws rds modify-db-instance \
  --db-instance-identifier <instance-id> \
  --backup-retention-period 7 \
  --preferred-backup-window "02:00-03:00"

aws rds create-db-snapshot \
  --db-instance-identifier <instance-id> \
  --db-snapshot-identifier <snapshot-name>

aws rds restore-db-instance-from-db-snapshot \
  --db-instance-identifier <new-instance-id> \
  --db-snapshot-identifier <snapshot-name>

{
  "Version": "2012-10-17",
  "Statement": [{
    "Effect": "Allow",
    "Action": ["s3:GetObject", "s3:ListBucket", "s3:PutObject", "s3:DeleteObject"],
    "Resource": [
      "arn:aws:s3:::<bucket-name>",
      "arn:aws:s3:::<bucket-name>/*"
    ]
  }]
}

{
  "Version": "2012-10-17",
  "Statement": [{
    "Effect": "Allow",
    "Principal": { "Service": "rds.amazonaws.com" },
    "Action": "sts:AssumeRole",
    "Condition": {
      "StringEquals": { "aws:SourceAccount": "<account-id>" },
      "ArnLike": { "aws:SourceArn": "arn:aws:rds:<region>:<account-id>:db:*" }
    }
  }]
}

aws rds add-role-to-db-instance \
  --db-instance-identifier <instance-id> \
  --role-arn arn:aws:iam::<account>:role/<role-name> \
  --feature-name S3_INTEGRATION

db2 "CATALOG STORAGE ACCESS ALIAS db2S3 VENDOR S3 
     SERVER https://s3.<region>.amazonaws.com 
     CONTAINER <bucket-name> 
     DBUSER <masterUserName>"

db2 "CATALOG STORAGE ACCESS ALIAS db2S3 VENDOR S3 
     SERVER s3.<region>.amazonaws.com 
     USER $AWS_ACCESS_KEY_ID 
     PASSWORD $AWS_SECRET_ACCESS_KEY 
     CONTAINER <bucket-name> 
     DBUSER <masterUserName> 
     TOKEN $AWS_SESSION_TOKEN"

# 5 parallel streams → produces .001 .002 .003 .004 .005
db2 backup database <DBNAME> to DB2REMOTE://db2S3, DB2REMOTE://db2S3, DB2REMOTE://db2S3, DB2REMOTE://db2S3, DB2REMOTE://db2S3

# For smaller databases, still use multi-part (minimum 5, up to 20 for large DBs)
# Single-part backup is NOT recommended — S3 streaming is less efficient

# Multi-part to local disk
db2 backup database <DBNAME> to /backup, /backup, /backup, /backup, /backup

# Copy to S3
aws s3 cp /backup/ s3://<bucket>/<prefix>/ --recursive

call rdsadmin.set_configuration('RESTORE_DATABASE_NUM_BUFFERS', '100');
call rdsadmin.set_configuration('RESTORE_DATABASE_PARALLELISM', '10');
call rdsadmin.set_configuration('RESTORE_DATABASE_NUM_MULTI_PATHS', '5');
call rdsadmin.set_configuration('USE_STREAMING_RESTORE', 'TRUE');

call rdsadmin.restore_database(
  '<DBNAME>',          -- target database name
  'OFFLINE',           -- backup type
  '<s3-prefix>',       -- common prefix of backup files (excluding .001, .002, etc.)
  '<bucket-name>',     -- S3 bucket
  '<region>'           -- AWS region
);

call rdsadmin.restore_database(
  '<DBNAME>',
  'ONLINE',
  '<s3-prefix>',
  '<bucket-name>',
  '<region>'
);
-- Database is now in rollforward-pending state

call rdsadmin.rollforward_database(
  '<DBNAME>',
  '<log-s3-prefix>',   -- prefix for archive log files in S3
  '<bucket-name>',
  '<region>'
);
-- Repeat as needed until all logs applied

call rdsadmin.complete_rollforward('<DBNAME>');

aws rds restore-db-instance-to-point-in-time \
  --source-db-instance-identifier <source-instance-id> \
  --target-db-instance-identifier <new-instance-id> \
  --restore-time 2024-01-15T08:00:00Z

aws rds create-db-instance-automated-backup-replication \
  --source-db-instance-arn arn:aws:rds:<source-region>:<account>:db:<instance-id> \
  --backup-retention-period 7 \
  --region <destination-region>

-- Connect to RDSADMIN first
db2 connect to RDSADMIN user <master-user> using '<password>'

-- Check task status
SELECT VARCHAR(task_type,25) AS task_type,
       VARCHAR(lifecycle,15) AS lifecycle,
       created_at,
       completed_work_bytes
FROM TABLE(rdsadmin.get_task_status(null,null,null)) AS r
ORDER BY created_at DESC;

-- Check task output (most recent)
SELECT VARCHAR(r.task_type,25) AS task_type,
       VARCHAR(r.lifecycle,15) AS lifecycle,
       VARCHAR(bson_to_json(task_input_params),256) AS input_params,
       VARCHAR(r.task_output,1024) AS task_output
FROM TABLE(rdsadmin.get_task_status(null,null,null)) AS r
ORDER BY created_at DESC FETCH FIRST 1 ROW ONLY;

get_task_status
get_task_output