-- ONLINE Restore benötigt
-- UPDATE DATABASE CONFIGURATION FOR <database>
USING
logarchmeth1 'LOGRETAIN'/'TSM', ....
-- INCREMENTAL/DELTA Backup
-- UPDATE DATABASE CONFIGURATION FOR <database>
USING
trackmod Yes
Beispiele für Backup
Einfacher Backup OFFLINE
backup database <database> USER <userid>
USING
<password> [to <verzeichniss>
oder USE TSM]
COMPRESS
Einfacher Backup ONLINE
backup database <database> USER <userid>
USING
<password> ONLINE [to
<verzeichniss> oder USE
TSM] COMPRESS INCLUDE LOGS
Einfacher Backup INCREMENTAL
backup database <database> USER <userid>
USING
<password> INCREMENTAL [to
<verzeichniss> oder
USE TSM] COMPRESS
Einfacher Backup DELTA
backup database <database> USER <userid>
USING
<password> INCREMENTAL DELTA [to
<verzeichniss>
oder USE TSM] COMPRESS
Ab Version 9.7 hat sich der Defaultwert bei einem Onlinebackup von
EXCLUDE LOGS auf INCLUDE LOGS geändert !!
Das ist wichtig für alle, die kein USEREXIT haben, das die LOGS sichert.
Beispiele für Restore
-- ÜBER SADMIN VIEW
SELECT DISTINCT
'RESTORE
DATABASE ' CONCAT RTRIM
(CURRENT SERVER)
CONCAT CASE
WHEN UCASE (LOCATION) LIKE '%DB2TSM%' THEN ' USE TSM '
ELSE ' FROM ' CONCAT RTRIM (LOCATION)
END
CONCAT '
TAKEN AT '
CONCAT
CHAR (MAX (START_TIME))
CONCAT '
BUFFER 1024'
CONCAT '
REPLACE EXISTING'
CONCAT '
WITHOUT ROLLING
FORWARD'
CONCAT '
WITHOUT PROMPTING'
CONCAT ';'
FROM SYSIBMADM.DB_HISTORY
WHERE OPERATION = 'B'
AND SQLCODE IS NULL
GROUP BY LOCATION
;
-- Einfach Offline
restore db <dbname> [from
<verzeichniss> oder
USE TSM]
TAKEN
AT ......
newlogpath
/....
-- INTO neue Datenbank
REPLACE EXISTING WITHOUT
PROMPTING
WITHOUT ROLLINGFORWARD ;
-- Einfach Online
restore db <dbname> [from
<verzeichniss>
oder USE TSM]
TAKEN
AT ......
LOGTARGET
/neuer/logpath
newlogpath
/....
-- INTO neue Datenbank
REPLACE EXISTING WITHOUT
PROMPTING
;
ROLLFORWARD DATABASE <dbname> TO YYYY-MM-DD-HH.MM.SS
USING LOCAL
TIME AND COMPLETE ;
db2 "restore database <dbname> from /backup/ taken at ????????
on /datenbanken/<dbname> LOGTARGET /tmp WITHOUT
PROMPTING"
db2 "ROLLFORWARD DATABASE <dbname> TO end of logs AND COMPLETE OVERFLOW LOG PATH (/tmp)"
-- Einfach Online Encrypted Database
db2 "restore database <dbname> from /backup/ taken at
???????? on /datenbanken/<dbname> LOGTARGET /tmp ENCRYPT
WITHOUT PROMPTING"
db2 "ROLLFORWARD DATABASE <dbname> TO end of logs AND COMPLETE OVERFLOW LOG PATH (/tmp)"
-- Tablespaces
RESTORE DATABASE <dbname> TABLESPACE
(<ts1>,
<ts2>, <tsn> ....
)
ONLINE
USE TSM
TAKEN AT yyyymmddhhssmm
LOGTARGET /wohin/mit/den/logs
NEWLOGPATH /Neuer/Logpath
REPLACE EXISTING
WITHOUT PROMPTING
;
ROLLFORWARD DATABASE <dbname> TO
YYYY-MM-DD-HH.MM.SS USING
LOCAL TIME AND COMPLETE
TABLESPACE(<ts1>, <ts2>,
<tsn>,
....
)
;
-- Redirect
RESTORE DATABASE <dbname>
[USE TSM oder FROM
<Verzeichniss>
TAKEN AT yyyymmddhhssmm
LOGTARGET /wohin/mit/den/logs
NEWLOGPATH /Neuer/Logpath
ON D:\
DBPATH ON D:\
INTO <neue db>
REPLACE EXISTING
REDIRECT
-- GENERATE SCRIPT
/scripte/Redirect.sql ab V9
WITHOUT PROMPTING
;
SET TABLESPACE CONTAINERS FOR 0 IGNORE ROLLFORWARD CONTAINER OPERATIONS
USING
( FILE
/databases/<dbname>/<TSNAME1> 100000 ) ;
SET TABLESPACE CONTAINERS FOR 1 IGNORE ROLLFORWARD CONTAINER OPERATIONS
USING
( FILE
/databases/<dbname>/<TSNAME2> 100000 ) ;
SET TABLESPACE CONTAINERS FOR n IGNORE ROLLFORWARD CONTAINER OPERATIONS
USING
( FILE
/databases/<dbname>/<TSNAMEn> 10000 ) ;
RESTORE DATABASE <dbname> CONTINUE;
--ROLLFORWARD DATABASE <dbname> TO END OF LOGS AND STOP
OVERFLOW
LOG PATH (C:\Temp);
-- ODER
ROLLFORWARD DATABASE <dbname> TO
YYYY-MM-DD-HH.MM.SS USING
LOCAL TIME AND COMPLETE ;
Rollforward :
Bis ende der Logs :
ROLLFORWARD DATABASE <dbname> TO END OF LOGS AND STOP OVERFLOW
LOG PATH (/tmp)
Bis zu einem bestimmten Zeitpunkt :
ROLLFORWARD DATABASE <dbname> TO 2012-09-03-07.12.47.000000 USING
LOCAL TIME AND COMPLETE