System to automate schema creation for table restore

• US 7,200,621 B2
• Filed: 12/17/2003
• Issued: 04/03/2007
• Est. Priority Date: 12/17/2003
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Beck, Kevin L., Gopalakrishnan, Vijay, Collins, Paula N., Zirn, Tania M.
• Primary Examiner(s): Cottingham; John
• Assistant Examiner(s): Lu; Kuen S.
• Application Number:
  US10/738,294
• Publication Number:
  US 20050138087A1
• Time in Patent Office: 1,203 Days
• Field of Search: 707/204, 707/102
• US Class Current: 707/661
• CPC Class Codes: G06F 11/1458 Management of the backup or restore process
• CPC Class Codes: G06F 11/1469 Backup restoration techniques
• CPC Class Codes: G06F 2201/80 Database-specific techniques
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

A method and system are provided to enable restoration of a base table and associated catalog table from a database. Table names and table identifiers are copied to a header of a storage media during a backup of the database. When a restore of a designated table is requested, table identifier data is extracted from the header of the storage media housing the backup of the database. Accordingly, the designated base table and associated catalog table(s) are restored from the storage media based upon data stored in the header of the storage media during the backup of the database.

Claims

1. A method comprising:

copying all table metadata, including table name and table identifier data, to a header of a backup storage media during a backup process of a database;

restoring a select base table from said backup storage media, comprising;

creating an empty copy of a catalog table associated with a select base table;

creating an empty copy of said select base table; and

reading said header from said storage media housing said backup of said database, including extracting table identifier metadata from said header;

identifying said select base table and all associated base tables;

reading said backup of said database from said storage media in physical order, including

extracting an extent list of physical page numbers for said specified base table and associated catalog tables; and

populating said empty copy of said select base table and associated catalog tables with backup data extracted from said storage media.

2. The method of claim 1, further comprising reading and applying log records to said restored base table and associated catalog tables.

3. The method of claim 2, wherein the step of reading and applying log records to the restored tables includes intervening changes to table schema to reflect any changes to said tables from time of creation of said backup to a concurrent log of said database.

4. The method of claim 1, further comprising cleaning up said restored catalog table, including extracting catalog table metadata for said subject base table from said restored copy of said catalog table, inserting said metadata into a corresponding live catalog table, and destroying said restored catalog table.

5. A computer system comprising:

a processor;

a backup manager in communication with said processor and adapted to copy table name metadata, including table identifier data, to a header of a storage media during a backup process of a database;

a restore manager in communication with said processor and adapted

to restore a select base table from said backup storage media through

creation of an empty copy of a catalog table associated with a select base table,

creation of an empty copy of said select base table, and

read said header from said storage media housing said backup of said database,

wherein the read of said header includes an extraction of table identifier metadata; and

said restore manager

to identify said select base table and all associated base tables,

to read said backup of said database from said storage media in physical order,

including extraction of an extent list of physical page numbers for said specified base table and associated catalog tables, and

to populate said empty copy of said select base table and associated catalog tables with backup data extracted from said storage media.

6. The system of claim 5, further comprising a data manager adapted to read and apply log records of a current database to said restored base table and a restored catalog table.

7. The system of claim 6, wherein said data manager is adapted to include changes to table schema from said current database into said restored database to include intervening changes to table schema to reflect any changes to said tables from time of creation of said backup to a concurrent log of said database.

8. The system of claim 5, further comprising a clean up said restored catalog table, including extraction of catalog table metadata for said subject base table from said restored copy of said catalog table, insertion of said metadata into a corresponding live catalog table, and destruction of said restored catalog table.

9. An article comprising:

a computer-readable data storage medium;

means in the medium for copying metadata, including table name and table identifier data, to a header of a storage media during a backup process of a database;

means in the medium for restoring a select base table from said backup storage media, comprising;

creating an empty copy of a catalog table associated with a select base table;

creating an empty copy of said select base table; and

reading said header from said storage media housing said backup of said database, including extracting table identifier metadata from said header;

means in the medium for identifying said select base table and all associated base tables;

means in the medium for reading said backup of said database from said storage media in physical order, including extracting an extent list of physical page numbers for said specified base table and associated catalog tables; and

means in the medium for populating said empty copy of said select base table and associated catalog tables with backup data extracted from said storage media.

10. The article of claim 9, further comprising means in the medium for reading and applying log records to said restored base table and a restored catalog table.

11. The article of claim 10, wherein the means for reading and applying log records to said restored tables includes changes to table schema to reflect any changes to said tables from time of creation of said backup to a concurrent log of said database.

12. The article of claim 9, further comprising cleaning up said restored catalog table, including extracting catalog table metadata for said subject base table from said restored copy of said catalog table, inserting said metadata into a corresponding live catalog table, and destroying said restored catalog table.