Table level database restore in a data storage system

• US 9,720,787 B2
• Filed: 09/30/2013
• Issued: 08/01/2017
• Est. Priority Date: 01/11/2013
• Status: Active Grant

• Current Assignee: CommVault Systems Incorporated
• Original Assignee: CommVault Systems Incorporated
• Inventors: Kumarasamy, Paramasivam, Vallabhaneni, Brahmaiah
• Primary Examiner(s): Beausoliel, Jr., Robert
• Assistant Examiner(s): Ganger, Arthur
• Application Number:
  US14/041,817
• Publication Number:
  US 20140201161A1
• Time in Patent Office: 1,401 Days
• Field of Search: 707679
• CPC Class Codes: G06F 11/1448 Management of the data involved in backup or backup restore
• CPC Class Codes: G06F 11/1469 Backup restoration techniques
• CPC Class Codes: G06F 2201/80 Database-specific techniques

Abstract

The data storage system according to certain aspects can implement table level database restore. Table level database restore may refer to restoring a database table and its related data without restoring the entire database. The data storage system may use table metadata index to implement table level restore. A table metadata index may be created for each table, e.g., during a backup of the database. The table metadata index for a table can include any type of information for restoring the table and its related data. Some examples of the type of information included in the table metadata index include the following: container for the table, table backup location, system data, table index, table relationships, etc. Table metadata index can make the restoring of tables fast and efficient by packaging information that can be used to restore a table and its related data in an easily accessible manner.

Claims

1. A method of restoring a database table from secondary storage in a data storage system, comprising:

using one or more computing devices comprising computer hardware;

receiving instructions to perform a table level restore for a first table of a plurality of database tables to restore the first table from a secondary storage subsystem to a primary storage subsystem,

wherein the plurality of database tables is associated with a database application installed on one or more client computing devices comprising computer hardware, and wherein a first secondary copy of data corresponding to a first subset of database tables belonging to a first tablespace is stored in the secondary storage subsystem and comprises data relating to the first subset of database tables, and a second secondary copy of data corresponding to a second subset of database tables belonging to a second tablespace different than the first tablespace is stored in the secondary storage subsystem and comprises data relating to the second subset of database tables, the first table belonging to the first tablespace;

accessing a first table metadata index for the first table, the first table metadata index comprising information specific to the first table, the first table metadata index further comprising information for restoring (i) the first table, (ii) a second table referenced by the first table and belonging to the first tablespace, and (iii) a third table referenced by the first table and belonging to the second tablespace, the first table metadata index further comprising (i) a first location within the first secondary copy that contains data corresponding to the first table, a second location within the first secondary copy that contains data corresponding to the second table, and a third location within the second secondary copy that contains data corresponding to the third table or (ii) information sufficient to locate data corresponding to the first and second tables in the first secondary copy and to locate data corresponding to the third table in the second secondary copy, wherein the first table metadata index was created prior to the receipt of the instructions to restore the first table; and

restoring the first table and the second table referenced by the first table from the secondary storage subsystem to the primary storage subsystem without restoring the entire first secondary copy, and restoring the third table referenced by the first table from the secondary storage subsystem to the primary storage subsystem without restoring the entire second secondary copy.

2. The method of claim 1, wherein the first secondary copy of data corresponding to the first subset of database tables belonging to the first tablespace is stored in a first storage device in the secondary storage subsystem, and the second secondary copy of data corresponding to the second subset of database tables belonging to the second tablespace is stored in a second storage device in the secondary storage subsystem that is different from the first storage device.

3. The method of claim 1, wherein the first table metadata index further comprises information relating to one or more of:

a container for the first table, one or more system objects or one or more database objects referenced by the first table and a table index usable to search and retrieve data in the first table.

4. The method of claim 1, further comprising:

restoring data corresponding to the first table by accessing the information relating to a backup location of the first table in the secondary storage subsystem in the first table metadata index;

restoring one or more system objects or one or more database objects referenced by the first table by accessing the information relating to system data in the first table metadata index; and

restoring data corresponding to the second and third tables referenced by the first table by accessing the information relating to a relationship between the first table and the second and third tables in the first table metadata index.

5. The method of claim 4, wherein subsequent to said restoring the first table and the second and third tables referenced by the first table, the restored first table in the primary storage subsystem is available to be browsed or queried and the data corresponding to the second and third tables referenced by the first table is available in the primary storage subsystem.

6. The method of claim 1, wherein the first table metadata index is packaged in a single file to facilitate the table level restore for the first table.

7. The method of claim 1, further comprising:

copying system data associated with the database application from the secondary storage subsystem to an auxiliary database;

copying a container that includes the data corresponding to the first table from the secondary storage subsystem to the auxiliary database by accessing the first table metadata index;

extracting from the auxiliary database the data corresponding to the first table and at least a portion of the system data referenced by the first table; and

copying the extracted data to the primary storage subsystem.

8. The method of claim 1, further comprising:

copying system data associated with the database application from the secondary storage subsystem to an auxiliary database;

copying a first container that includes the data corresponding to the first and second tables and a second container that includes the data corresponding to the third table from the secondary storage subsystem to the auxiliary database by accessing the first table metadata index;

extracting from the auxiliary database the data corresponding to the first, second and third tables and at least a portion of the system data referenced by the first, second, and third tables; and

copying the extracted data and the portion of the system data to the primary storage subsystem.

9. The method of claim 1, wherein the first table metadata index is available in an index that is not stored in the secondary storage subsystem.

10. The method of claim 1, wherein the first table metadata index does not include information for restoring a fourth table of the plurality of database tables that is not referenced by the first table.

11. The method of claim 1, wherein neither of the first table and the third table is referenced by the second table, the method further comprising accessing, in response to receiving instructions to perform a table level restore for the second table, a second table metadata index for the second table, the second table metadata index comprising information for restoring the second table but not including information for restoring the first table or the third table.

12. The method of claim 1, wherein only information needed to restore the first table is included in the first table metadata index to facilitate the table level restore for the first table.

13. The method of claim 1, wherein said restoring comprises restoring the first table and the second and third tables referenced by the first table from the secondary storage subsystem to the primary storage subsystem without restoring the entire secondary copy, and without restoring all of the plurality of database tables.

14. A data storage system for restoring a database table from secondary storage subsystem, comprising:

one or more media agents comprising computer hardware and configured to copy data from a secondary storage subsystem to a primary storage subsystem; and

one or more computing devices comprising computer hardware and configured to;

receive instructions to perform a table level restore for a first table of a plurality of database tables to restore the first table from the secondary storage subsystem to the primary storage subsystem,

wherein the plurality of database tables is associated with a database application installed on one or more client computing devices comprising computer hardware, and wherein a first secondary copy of data corresponding to a first subset of database tables belonging to a first tablespace is stored in the secondary storage subsystem and comprises data relating to the first subset of database tables, and a second secondary copy of data corresponding to a second subset of database tables belonging to a second tablespace different than the first tablespace is stored in the secondary storage subsystem and comprises data relating to the second subset of database tables, the first table belonging to the first tablespace;

access a first table metadata index for the first table, the first table metadata index comprising information specific to the first table, the first table metadata index further comprising information for restoring (i) the first table, (ii) a second table referenced by the first table and belonging to the first tablespace, and (iii) a third table referenced by the first table and belonging to the second tablespace, the first table metadata index further comprising (i) a first location within the first secondary copy that contains data corresponding to the first table, a second location within the first secondary copy that contains data corresponding to the second table, and a third location within the second secondary copy that contains data corresponding to the third table or (ii) information sufficient to locate data corresponding to the first and second tables in the first secondary copy and to locate data corresponding to the third table in the second secondary copy, wherein the first table metadata index was created prior to the receipt of the instructions to restore the first table; and

restore, using the one or more media agents, the first table and the second table referenced by the first table from the secondary storage subsystem to the primary storage subsystem without restoring the entire first secondary copy, and restore the third table referenced by the first table from the secondary storage subsystem to the primary storage subsystem without restoring the entire second secondary copy.

15. The system of claim 14, wherein the first secondary copy of data corresponding to the first subset of database tables belonging to the first tablespace is stored in a first storage device in the secondary storage subsystem, and the second secondary copy of data corresponding to the second subset of database tables belonging to the second tablespace is stored in a second storage device in the secondary storage subsystem that is different from the first storage device.

16. The system of claim 14, wherein the first table metadata index further comprises information relating to one or more of:

a container for the first table, one or more system objects or one or more database objects referenced by the first table, and a table index usable to search and retrieve data in the first table.

17. The system of claim 14, wherein the one or more computing devices are further configured to:

restore data corresponding to the first table by accessing the information relating to a backup location of the first table in the secondary storage subsystem in the first table metadata index;

restore one or more system objects or one or more database objects referenced by the first table by accessing the information relating to the system data in the first table metadata index; and

restore data corresponding to the second and third tables referenced by the first table by accessing the information relating to a relationship between the first table and the second and third tables in the first table metadata index.

18. The system of claim 17, wherein subsequent to the restoring of data corresponding to the first table and the second and third tables referenced by the first table, the restored first table in the primary storage subsystem is available to be browsed or queried and the data corresponding to the second and third tables referenced by the first table is available in the primary storage subsystem.

19. The system of claim 14, wherein the restoring of the first table and the one or more tables referenced by the first table comprises:

copying system data associated with the database application from the secondary storage subsystem to an auxiliary database;

copying a container that includes the data corresponding to the first table from the secondary storage subsystem to the auxiliary database by accessing the first table metadata index;

extracting from the auxiliary database the data corresponding to the first table and at least a portion of the system data referenced by the first table; and

copying the extracted data to the primary storage subsystem.

20. The system of claim 14, wherein the restoring of the first table and the one or more tables referenced by the first table comprises:

copying system data associated with the database application from the secondary storage subsystem to an auxiliary database;

copying a first container that includes the data corresponding to the first and second tables and a second container that includes the data corresponding to the third table from the secondary storage subsystem to the auxiliary database by accessing the first table metadata index;

extracting from the auxiliary database the data corresponding to the first, second, and third tables and at least a portion of the system data referenced by the first, second, and third tables; and

copying the extracted data and the portion of the system data to the primary storage subsystem.