Question & Answer
Question
How do you backup and restore an offline database using Windows® ntbackup ?
Cause
There is documentation about how to perform online backups and restore database using ESS FlashCopy but there is no similar documentation about how to perform offline backup and restore of databases using Windows® ntbackup.
Answer
This guide describes how to backup and restore offline databases using Windows ntbackup and apply the latest updates using rollforward recovery utility with the new logs which were created after taking the offline backup.
Note:
A) Make sure all files systems containing the database are backed up. If multiple databases are sharing the same set of file systems, then backup/restore of the file system for one particular database may cause the other databases to become inconsistent. In this case, all the databases must be suspended, backed up and then restored at the same time to prevent inconsistency from happening.
B) You can apply newer transactions to the restored database through rollforward recovery operation with new logs copied over from the primary database server. At that time, make sure you don't add any transactions to the restored database before rollforward.
C) You need to copy the following path/files for offline backup using Windows ntbackup.
* Tablespace containers (DMS)
To determine which containers to copy:
1. Issue the command: LIST TABLESPACES
2. Make note of the table space IDs.
3. For each table space ID, issue the command: LIST TABLESPACE CONTAINERS FOR <id>
* Tablespace directory (SMS)
To determine which containers to copy, issue the same commands as described above.
* Database directory path
This path can be determined as follows:
1. Connect to the database: CONNECT TO <database name>
2. Issue the command: LIST ACTIVE DATABASES
3. Refer to the "Database path" values in the output.
* System database directory (sqldbdir)
You can get this information by replacing the string "SQLnnnnn" of the above "database directory path" with "SQLDBDIR".
* AutoStorage Path
"Automatic storage path" of database snapshot output.
If you use DB2 Version 9.1 or above, you can get the values for database paths required for tasks such as split mirror backups except "archive log path" with the following command.
- db2 "select * from SYSIBMADM.DBPATHS"
* Archive log path
This path is found in the LOGARCHMETH1 and LOGARCHMETH2 database configuration parameter, or in your user exit program.
Note:
The active log path which is represented with "Path to log files" or "MIRRORLOGPATH" in the database configuration parameter or "LOGPATH" in SYSIBMADM.DBPATHS should not be included in the data to be copied because any transaction log records are not remained there during the Db2 instance is not activated and transaction log files to be archived may be remained, which should not be copied back.
And the archive log path should be copied into the other image rather than the database backed up image because the latest archive log path should not be overwrite when the database image is copied back.
D) You need back up user programs (applications, stored procedures, udf and userexit) because you need to use the same programs after restoring.
E) When you restore images to a different server, you will need to restore them into the same named instance on the target server as on the original one. Backing up the database is recommended after the restore operation.
The mount points on the target server must be identical or else the container paths may not work.
Ensure that you have the same configured system for this operation since the file access permission attributes are built with the system user ID and group ID.
Procedure:
1) Taking offline backups using Windows ntbackup
Note: You may need to setup proper instance name by setting "DB2INSTANCE" environment variable before issuing these steps.
1-1) db2stop force
1-2) Backup database files using Windows ntbackup
Note: Refer to the above notifications to know which files should be copied by Windows ntbackup
1-3) db2start
2) Restoring offline backups
Note: You may need to setup proper instance name by setting "DB2INSTANCE" environment variable before issuing these steps.
2-1) db2stop force
2-2) Restoring backed up files at taken at 1-2) by Windows ntbackup
Note: You should verify restored files and directories are the same as the ones that were backed up
2-3) Restore user programs which were backed up on Note: D) listed above and check them
2-4) db2start
3) Restoring offline backups and rolling forward database
Note: You may need to setup proper instance name by setting "DB2INSTANCE" environment variable before issuing these steps.
3-1) db2stop force
3-2) Save the archive logs
Note: If there are some active log files after 1-1), please copy them to another directory for backup.
3-3) Restoring backed up files taken after 1-2) by Windows ntbackup
Note: You should verify restored files and directories are the same as the ones that were backed up
3-4) Restore log files which are backed up on the step above
3-5) db2rfpen on <dbname>
3-6) db2start
3-7) db2 rollforward db <database name> to end of logs
3-8) db2 rollforward db <database name> stop
3-9) db2stop
3-10) Restore user programs which is backed up on Note: D) listed above and check them
3-11) db2start
3-12) db2 connect to <database name>
Related Information
technote(FAQ) 1367147 - Database recovery using the file system copy command du…
DBPATHS administrative view and ADMIN_LIST_DB_PATHS table function - Retrieve d…
mirrorlogpath - Mirror log path configuration parameter
GET DATABASE CONFIGURATION command
logarchmeth1 - Primary log archive method configuration parameter
Was this topic helpful?
Document Information
Modified date:
24 October 2018
UID
swg21269232