Oops! Restoring your database with Data Studio Administrator
New capabilities in IBM Data Studio Administrator enable point-in-time recovery
Almost every DBA has had that sinking feeling when you realize that circumstances beyond your control have damaged or caused the loss of data in your database. Suddenly your only priority becomes helping to recover from the unexpected situation. Anything you can use to make the recovery task easier serves to shorten the length of the outage. In the long term, having recovery management become a more natural part of day-to-day activities reduces stress when the inevitable does occur and enables you to recover more quickly.
This article tells the story of a DBA named Erik. Erik works at JK Enterprises Sample Outdoors (a fictitious company) and uses IBM Data Studio Administrator to back up the company's critical sales database. After bad application logic accidentally deletes data from multiple tables, Erik comes to the rescue and restores the database. He is also able to roll the database forward to a point in time just before the application did its damage.
The capabilities described in this article are available with the no charge version of Data Studio Administrator, which you can obtain by downloading the Data Studio Administrator trial and not enabling the Database Change Management feature. The advantage you gain by installing and buying the Database Change Management feature is that it provides support for extended alters, including data preservation and management of dependent objects such as constraints, views, triggers and referential integrity.
Here is an overview of the backup and recovery steps that are described in this article:
- If not connected, establish a connection to the affected database.
- View data to see what rows you have in your tables.
- Use the backup task assistant to back up your system.
- Run sample delete statements to reproduce the bad application logic that deleted all rows in two tables.
- Use the restore task assistant to restore the database and roll forward to a point in time before the delete statements were run.
- Look at updated row counts to verify that the data is intact after the restore operation.
Use these steps to install and configure an environment you can use to follow along with Erik's scenario:
- The scenario requires that you have Data Studio Administrator Version 2 Release 1
with Fix Pack 1 installed.
If you do not have Data Studio Administrator Version 2 Release 1 installed, use the link in the Related topics section to download the trial version of the product.
If you have not installed Fix Pack 1 onto your Data Studio Administrator Version 2 Release 1 system, use the link in the Related topics to get the Fix Pack 1 installation files and instructions.
- You must also have a supported version of DB2® installed. If you do not have DB2, use the link in the Related topics section to download DB2 Express-C, which is a no-charge version of DB2 Express Edition.
- Follow these steps to create a sample database named GSDB:
- Get the GSDB_Database.zip file from the Download section of this article and extract the GSDB_Database.sql file from it.
- Open a DB2 Command Window.
- Navigate to the location where you saved the GSDB_Database.sql file.
Enter the following command:
db2 -td~ -f GSDB_Database.sql
Change from default circular logging to use archive logging. To use archive logging, you must use the UPDATE DATABASE CONFIGURATION command to set LOGARCHMETH1 to a value other than OFF, as shown here:
UPDATE DB CFG FOR GSDB USING logarchmeth1 "DISK:C:\GSDB_ARCHIVE" failarchpath \ "C:\GSDB_failure" logprimary 2 logsecond 0 logfilsiz 1024;
Note: The backslash character at the end of the first line in the command shown above is used only as a mechanism to indicate a line continuation. Do not enter the backslash as not part of the command. You would actually enter the command as one continuous string in the DB2 Command Window.
The above command specifies:
- Use archive logging.
Save archive logs to the
If archive logging fails, write to the temporary directory called
- There are two primary logs and no secondary logs.
- The primary log size is 1024 4KB pages.
During one of the steps of the scenario, you perform a full offline backup of the database after changing the logging option.
The following sections of the article describe the major events of the day for Erik and provide details of the actions he takes. The details are worded as step-by-step instructions that you can follow to gain your own hands-on learning experience.
Connect to the database
All Data Studio products use a common Data Source Explorer to connect to databases. The first task Erik performs in this scenario is to establish a connection to the GSDB database as follows:
In Data Source Explorer, right click on Database Connections and select New… as
shown in Figure 1.
Figure 1. Connecting to the database from Data Source Explorer
- Verify the connection by entering the required connection parameters and clicking the Test Connection
button as shown in Figure 2.
Figure 2. Verify the connection
- If the connection is successful, click the Next button.
- From the Filter screen, make the selections to include the GOSALES
and GOSALESCT schemas and click the Finish button as shown in Figure 3.
Figure 3. Filtering by schemas
View number of rows in your table
Once connected to the GSDB database, Erik wants to view the row count of selected tables.
This scenario uses the row count as an indicator of the current state of the tables. After you perform the restore operation, you can use the count to verify that the table is back to this same state.
To view the row count of the selected tables, do the following:
- In Data Source Explorer, double click the Tables folder for the GSDB database
as shown in Figure 4.
Figure 4. Double-click on Tables
- The Object List window now shows the table schema, table name, and
row count for each table as shown in Figure 5.
Note that the GOSALES.PRODUCT and GOSALES.PRODUCT_FORECAST tables have
274 rows and 2728 rows, respectively.
Figure 5. Object List window contains row counts
Optionally, another way to do this while also being able to see the actual data, is to
right-click an individual table and choose Data > Return All Rows as
shown in Figure 6.
Figure 6. Returning all rows for an individual table
Back up the database
Erik now performs the following steps to back up the GSDB database:
- In the Data Source Explorer, right click on the GSDB database and select
Backup… (as shown in Figure 7) to launch the Backup task assistant.
Figure 7. Choose Backup from Data Source Explorer
- On the 1. Type tab, select Back up the entire
database as shown in Figure 8.
Figure 8. Choose backup type from Backup task assistant
- Select the 2. Image tab.
- Select File System as the media type from the dropdown menu (Figure 9).
- Click the Browse button and select C:\GSDB_BACKUP as the location for the
backup image (Figure 9).
Figure 9. Select backup media type and location
- Select the 3. Options tab.
- Select Full backup – Back up all data for Backup type (Figure 10).
- Select Offline - Users cannot access the database during the backup for Availability.
After making this selection, the quiesce checkbox option is enabled.
Select the checkbox (Figure 10).
to perform a full offline backup because after changing the logging type, this type of
backup is required in order to later be able to perform a restore to point in time.
Figure 10. Selecting backup options
Click Preview Command to generate and view the commands in the
Command window as shown in Figure 11.
Figure 11. Preview of backup commands
- Click the Run button .
- Data Studio Administrator should return a success message for the backup as shown
in Figure 12.
Figure 12. Message window shows success
Later during the day, Erik looks at the application logs and discovers that bad application logic has deleted all the rows from the GOSALES.PRODUCT and GOSALES.PRODUCT_FORECAST tables. He verifies this by going to the Object List window and seeing that the row count for both tables is 0 as shown in Figure 13. He knows there is a problem because these tables previously had 274 rows and 2728 rows, respectively (see Figure 5).
Figure 13. Two tables have all rows deleted
In order to follow along with Erik as he recovers his data, you first need to reproduce the deletion of the rows by entering the following DB2 commands:
delete from GOSALES.PRODUCT delete from GOSALES.PRODUCT_FORECAST
Make a note of the time so that you will be able to recover to a point before you executed the delete commands.
Restore to a point in time
Erik is now anxious to use the capabilities of Data Studio Administrator to restore the database to its proper condition. To put the database in its original state before the bad application logic occurred, Erik performs a restore of the database and a roll forward to a point in time by following these steps:
- In the Data Source Explorer, right click on the GSDB database and select Restore… (as shown in Figure 14) to launch the Restore task assistant.
Figure 14. Selecting Restore from Data Source Explorer
On the 1. Type tab, select Restore to an existing database and
Restore the entire database as shown in Figures 15 and 16.
Figure 15. Selection for restoring to an existing database
Figure 16. Selection for restoring the entire database
- Select the 2. Available Images tab.
- Select Select the backup images from a list (Figure 17).
- From the list of backup images, select the most recent image prior to when the bad
application ran (when you deleted the table rows) and click the right arrow button to move the backup
image to the right box (Figure 17).
Figure 17. Choose most recent image before bad application ran
- Select the 4. Roll forward tab.
- Select Restore the database and roll forward as follows (Figure 18).
- Select Roll forward to a point in time – Local (Figure 18).
- Enter appropriate values for the Roll forward to transaction date and time.
The date and time should be prior to when the bad
application ran (when you deleted the table rows).
Figure 18. Select roll forward time to before bad commands were executed
- Select the 5. Final State tab.
- Select Complete the restore and return the database to the active state as
shown in Figure 19.
Figure 19. Choosing to return database to active state after roll forward completes
Click Preview Command to generate and view the commands in the
Command window, as shown in Figure 20.
Figure 20. Preview of Restore commands
Click the Run button and watch the results appear in the Messages and SQL Results window as
shown in Figure 21.
Figure 21. Progress and status of the restore
Verify the restore
Erik lets out a sigh of relief! He has restored the database to the state before the bad application logic occurred. Erik verifies this by using Data Studio Administrator to check the table row counts as follows:
- In Data Source Explorer, double click the Tables folder.
- The Object List window now shows the GSDB database tables. Right click on the
GOSALES.PRODUCT table and select Update statistics as shown in Figure 22.
Figure 22. Choose Update Statistics to refresh row count
- Click in the SQL Results window. This refreshes the Object List window with current row counts.
- Use the same process to refresh the current row counts for the GOSALES.PRODUCT_FORECAST table (right click on the table, select Update statistics, click in the SQL Results window).
- As shown in Figure 23, the GOSALES.PRODUCT and GOSALES.PRODUCT_FORECAST tables are
now back to the same counts as before the bad application logic occurred
(274 rows and 2728 rows, respectively).
Figure 23. Rows have been Recovered
Data Studio Administrator provides database administrators with a useful tool to perform regularly scheduled backups as part of their backup and recovery strategy. These backups can be used to restore databases to their proper state after data corruption events, such as software failures, hardware failures, bad application logic, and user errors.
Erik has verified that the database was successfully recovered. With the crisis avoided, he decides to reward himself by taking a stroll to the corner café and enjoying an iced double tall caramel mocha latte.
- Understand how Data Studio Administrator fits in with the rest of the Data Studio portfolio in this article: “IBM Data Studio software: The big picture.”
- Understand Data Studio packaging, including the no-charge administrative capabilities available in the Data Studio Administrator download by reading this article: “Understanding Data Studio software packaging.”
- Find out all the new capabilities in Data Studio Administrator: “What’s new in Data Studio Administrator 2.1.”
- Download a trial version of Data Studio Administrator.
- Download and install Fix Pack 1 for Data Studio Administrator 2.1. The fix pack is required for the capabilities described in this article.
- Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.