Question: A developer corrupted some data and requested a restore to a previous recovery point. A standard request. When checking the backup system , there were no backup copies available. The backups had been failing for the last couple of nights and the monitoring alerts were not working.
I checked around the backup history file , in case any ad-hoc backups had completed, and there none available. As a last resort I checked if there had been a flat file backup , and amazingly there was a flat file backup of all the files on the server. This is unusual as normally DB2 files are excluded on flatfile backups.
Is it possible to recover the database just with the flat files?
Answer: Yes it is possible, but careful planning is required. Before you proceed with this procedure , you must confirm the flat files are recoverable and include all the files you need.These are the steps I would take
- Stop any application connections to the database
- Take a full OFFLINE backup of the db
- Take a copy of the get db cfg output , for reference
- Identify the folders required to be replaced. This is an important step.This will heavily depend on how the database was set up. I normally create a separate folder for the data files and log files.
- If you are unsure of step 4 – do some research or speak to another DBA , who can cross-check what folder you’re proposing to overwrite
- Stop the DB2 instance – making sure there are no auto starts or processes which will auto start the DB2 instance. It may also be necessary to notify any Operations teams to avoid alerts for this DB2 instance, just in case they attempt to restart
- Restore the files, it will be an overwrite of existing files.
- Before you restart the DB2 instance – you may be in luck and know the last active log file of the db you’re restoring. This will allow you to clean up any transaction log files which are from after this restore i.e related to the database you’re overwriting
- If you don’t have the last active log file number of the db you’re recovering, you could start the DB2 instance , quickly do a db2 get db cfg and obtain the information
- You could check for the first active log and calculate the last active log
- Do a connection test
- FIX YOUR MONITORING ALERT SYSTEM