Oops! Restoring your database with Data Studio Administrator

New capabilities in IBM Data Studio Administrator enable point-in-time recovery

As a DBA, it is crucial that you perform regularly scheduled database system backups as part of your backup and recovery strategy. In the event of an application or hardware failure, you can use these backups to restore your database. This article follows a day in the life scenario for a DBA named Erik, who works at a fictitious company. On this particular day, problems arise when Erik discovers that bad application logic has deleted rows from multiple tables of the company's GOSALES database. However, because Erik used Data Studio Administrator 2.1 to back up the database, he is able to restore the database to its original state.

Sailaja Bandlamoori, Staff Software Engineer, IBM

Sailaja Bandlamoori photoSailaja Bandlamoori is the QA Team Lead for Data Studio Administrator in the Information Management Data Studio group at IBM Silicon Valley Laboratory in San Jose, CA. She has worked on the Data Studio Administrator product since 2006. Prior to that, she worked in DB2 for z/OS client/server System Test.



Tawanna Harris, Staff Software Engineer, IBM

TaWanna Harris photoTaWanna Harris is a QA Staff Software Engineer in the Information Management Data Studio group at Lenexa, KS. She has worked on the Data Studio Administrator product since 2008. Prior to that, she worked on IBM Migration Toolkit, partner and customer application testing, and interoperability testing.



02 April 2009

Also available in

Introduction

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:

  1. If not connected, establish a connection to the affected database.
  2. View data to see what rows you have in your tables.
  3. Use the backup task assistant to back up your system.
  4. Run sample delete statements to reproduce the bad application logic that deleted all rows in two tables.
  5. Use the restore task assistant to restore the database and roll forward to a point in time before the delete statements were run.
  6. Look at updated row counts to verify that the data is intact after the restore operation.

Prerequisites

Use these steps to install and configure an environment you can use to follow along with Erik's scenario:

Why archive logging?

Recovery is the rebuilding of a database or table space after a problem such as media or storage failure, power interruption, or application failure. If you have backed up your database, or individual table spaces, you can rebuild them if they become damaged or corrupted in some way.

Circular logging means that the logs are continually being written over and are retained only as long as needed to ensure the integrity of current transactions. Because this type of restore operation recovers your data to the specific point in time at which a full backup was taken, it is called version recovery. This is the default logging option for database creation, which is why you have to explicitly change it before being able to follow the scenario described in this article.

To support roll forward recovery, which lets you reapply changes that were made after a backup was made, you must use archive logging. As the name suggests, archive logging ensures that you have the logs needed to recover to a specific point in time by applying changes through the appropriate archive and active logs.

For more details, refer to the "Understanding recovery logs" topic in the DB2 for Linux®, Unix®, and Windows® Information center.

  1. 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 Resources 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 Resources to get the Fix Pack 1 installation files and instructions.

  2. You must also have a supported version of DB2® installed. If you do not have DB2, use the link in the Resources section to download DB2 Express-C, which is a no-charge version of DB2 Express Edition.
  3. Follow these steps to create a sample database named GSDB:
    1. Get the GSDB_Database.zip file from the Download section of this article and extract the GSDB_Database.sql file from it.
    2. Open a DB2 Command Window.
    3. Navigate to the location where you saved the GSDB_Database.sql file.
    4. Enter the following command:
      db2 -td~ -f GSDB_Database.sql
  4. 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 GSDB_ARCHIVE directory.
    • If archive logging fails, write to the temporary directory called GSDB_failure.
    • 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:

  1. 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
    Data Source Explorer screen to create a new database connection
  2. 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
    Use the Connection Identification screen to enter connection parameters and test connection
  3. If the connection is successful, click the Next button.
  4. 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
    On filter screen include GOSALES and GOSALESCT 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:

  1. In Data Source Explorer, double click the Tables folder for the GSDB database as shown in Figure 4.
    Figure 4. Double-click on Tables
    Double clicking the Tables folder in Data Source Explorer
  2. 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
    Object List window shows the table schema, table name, and row count for each table
  3. 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
    Selecting Data > Return all Rows for a GOSALESCT table

Back up the database

Erik now performs the following steps to back up the GSDB database:

  1. 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
    From Data Source Explorer right click the GSDB database and select Backup...
  2. 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
    Making Type tab selections to backup the entire database
  3. Select the 2. Image tab.
  4. Select File System as the media type from the dropdown menu (Figure 9).
  5. 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
    Making Image tab selections for selecting file system as media type and the location for the backup image from Image tab of Backup assistant
  6. Select the 3. Options tab.
  7. Select Full backup – Back up all data for Backup type (Figure 10).
  8. 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). You need 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
    Making Options tab selections to perform a full offline backup
  9. Click Preview CommandPreview Command link to generate and view the commands in the Command window as shown in Figure 11.
    Figure 11. Preview of backup commands
    Preview of the backup commands generated by the Backup task assistant
  10. Click the Run button Run button.
  11. Data Studio Administrator should return a success message for the backup as shown in Figure 12.
    Figure 12. Message window shows success
    Back up success message returned from Data Studio Administrator

Oops!

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
Object list window now shows 0 rows for GOSALES.PRODUCT and GOSALES.PRODUCT_FORECAST tables

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:

  1. 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
Selecting Restore from Data Source Explorer
  1. 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
    Type tab selection for Restore to an existing database
    Figure 16. Selection for restoring the entire database
    Type tab selection for Restore the entire database
  2. Select the 2. Available Images tab.
  3. Select Select the backup images from a list (Figure 17).
  4. 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 Right arrow button to move the backup image to the right box (Figure 17).
    Figure 17. Choose most recent image before bad application ran
    Using the Available Images tab to select the most recent backup image prior to the bad application running
  5. Select the 4. Roll forward tab.
  6. Select Restore the database and roll forward as follows (Figure 18).
  7. Select Roll forward to a point in time – Local (Figure 18).
  8. 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
    Using the Roll forward tab to select a time prior to the table rows being deleted
  9. Select the 5. Final State tab.
  10. 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
    Using the Final State tab to choose to return the database to active state after the roll forward
  11. Click Preview CommandPreview Command link to generate and view the commands in the Command window, as shown in Figure 20.
    Figure 20. Preview of Restore commands
    Preview of the restore commands generated by the Restore task assistant
  12. Click the Run button 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
    Messages indicating the progress of the restore operation and finally that it succeeded

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:

  1. In Data Source Explorer, double click the Tables folder.
  2. 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
    Choosing Update Statistics from context menu after right click on database table
  3. Click in the SQL Results window. This refreshes the Object List window with current row counts.
  4. 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).
  5. 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
    Updated row counts in Object List window verify successful restore

Conclusion

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.


Download

DescriptionNameSize
GSDB sample database for this articleGSDB_database.zip284KB

Resources

Learn

Get products and technologies

  • 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.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=379948
ArticleTitle=Oops! Restoring your database with Data Studio Administrator
publish-date=04022009