Table level database restore in a data storage system

• US 9,766,987 B2
• Filed: 09/30/2013
• Issued: 09/19/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): Perveen, Rehana
• Assistant Examiner(s): Skhoun, Hicham
• Application Number:
  US14/041,771
• Publication Number:
  US 20140201160A1
• Time in Patent Office: 1,450 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 storing database tables in secondary storage of a data storage system, comprising:

using one or more computing devices comprising computer hardware;

copying data associated with a database application, residing on one or more primary storage devices in a primary storage subsystem, to one or more secondary storage devices in a secondary storage subsystem during a secondary storage operation in order to create a secondary copy of the data associated with the database application, the database application installed on one or more client computing devices comprising computer hardware and in communication with the primary storage subsystem,

wherein the data associated with the database application comprises data corresponding to a database comprising a plurality of database tables, the plurality of database tables including at least a first table and a second table; and

during the secondary storage operation;

determining a first set of database tables referenced by the first table from the plurality of database tables;

packaging (i) information for restoring the first table and (ii) information for restoring the first set of database tables referenced by the first table, together in a first table metadata index specific to the first table for use in restoring the first table such that the first table can be restored without restoring all of the plurality of database tables or accessing other metadata indices, thereby allowing a more efficient and quicker restore of the first table;

determining a second set of database tables referenced by the second table from the plurality of database tables;

packaging (i) information for restoring the second table and (ii) information for restoring the second set of database tables referenced by the second table, together in a second table metadata index specific to the second table and separate from the first table metadata index for use in restoring the second table such that the second table can be restored without restoring the first table or accessing the first table metadata index, thereby allowing a more efficient and quicker restore of the second table; and

storing the first table metadata index and the second metadata index on the one or more secondary storage devices, or in a data structure associated with one or more media agents comprising computer hardware, or in a data structure associated with one or more storage managers comprising computer hardware.

2. The method of claim 1, wherein the information for restoring the first table packaged in the first table metadata index of the first table indicates a first location within the secondary copy where data associated with the first table is stored, and the information for restoring the first set of database tables indicates a respective location within the secondary copy that is different from the first location for each database table in the first set where data associated with the database table is stored.

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

a system object referenced by the first table or a database object referenced by the first table.

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

a container for the first table, a tablespace for the first table, a table schema of the first table, a backup location of the first table in the secondary storage subsystem, system data associated with the first table, a table index associated with the first table, or a relationship between the first table and the first set of database tables of the plurality of database tables.

5. The method of claim 4, wherein the system data comprises one or more of:

a system object, a database object, a database schema associated with the database application, or a data structure used by the database application.

6. The method of claim 4, wherein the relationship between the first table and the first set of database tables of the plurality of database tables comprises a list of the first set of database tables referenced by the first table.

7. The method of claim 4, wherein the relationship between the first table and the first set of database tables of the plurality of database tables comprises metadata relating to the first set of database tables of the plurality of database tables, the metadata comprising the same type of information as the information included in the first table metadata index.

8. The method of claim 4, wherein data relating to the first table is stored in a secondary copy of the tablespace that includes the first table, and wherein the backup location of the first table indicates a location of the data relating to the first table within the secondary copy of the tablespace.

9. The method of claim 1, further comprising restoring the first table from the secondary storage subsystem to the primary storage subsystem by accessing the first table metadata index, without restoring the second table or accessing the second table metadata index.

10. The method of claim 9, wherein subsequent to said restoring, data of the first set of database tables referenced by the first table are accessible in the primary storage subsystem, and the restored first table is available for being browsed or queried using the database application.

11. The method of claim 1, wherein the determination of the first set of database tables and the packaging of the information in the first table metadata index are performed during a backup of data from the primary storage subsystem to the secondary storage subsystem.

12. The method of claim 1, further comprising copying the first table metadata index and the second table metadata index to the secondary storage subsystem.

13. The method of claim 1, wherein the first table metadata index and the second table metadata index are not stored in the secondary storage subsystem.

14. A data storage system for storing database tables, comprising:

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

copy data associated with a database application, residing on one or more primary storage devices in a primary storage subsystem to one or more secondary storage devices in a secondary storage subsystem during a secondary storage operation in order to create a secondary copy of the data associated with the database application, the database application installed on one more client computing devices comprising computer hardware and in communication with the primary storage subsystem,

wherein the data associated with the database application comprises data corresponding to a database comprising a plurality of database tables, the plurality of database tables including at least a first table and a second table; and

during the secondary storage operation;

determine a first set of database tables referenced by the first table from the plurality of database tables;

package (i) information for restoring the first table and (ii) information for restoring the first set of database tables referenced by the first table, together in a first table metadata index specific to the first table for use in restoring the first table such that the first table can be restored without restoring all of the plurality of database tables or accessing other metadata indices, thereby allowing a more efficient and quicker restore of the first table;

determine a second set of database tables referenced by the second table from the plurality of database tables;

package (i) information for restoring the second table and (ii) information for restoring the second set of database tables referenced by the second table, together in a second table metadata index specific to the second table and separate from the first table metadata index for use in restoring the second table such that the second table can be restored without restoring the first table or accessing the first table metadata index, thereby allowing a more efficient and quicker restore of the second table; and

store the first table metadata index and the second metadata index on the one or more secondary storage devices, or in a data structure associated with one or more media agents comprising computer hardware, or in a data structure associated with one or more storage managers comprising computer hardware.

15. The system of claim 14, further comprising a data agent installed on the one or more client computing devices, wherein the one or more computing devices are further configured to query the data agent to determine a relationship between the first table and the first set of database tables of the plurality of database tables.

16. The system of claim 14, wherein the information for restoring the first table packaged in the first table metadata index of the first table indicates a first location within the secondary copy where data associated with the first table is stored, and the information for restoring the first set of database tables indicates a respective location within the secondary copy that is different from the first location for each database table in the first set where data associated with the database table is stored.

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

a container for the first table, a tablespace for the first table, a table schema of the first table, a backup location of the first table in the secondary storage subsystem, system data associated with the first table, a table index associated with the first table, or a relationship between the first table and the first set of database tables of the plurality of database tables.

18. The system of claim 14, wherein the one or more computing devices are further configured to restore the first table from the secondary storage subsystem to the primary storage subsystem by accessing the first table metadata index, without restoring the second table or accessing the second table metadata index.

19. The system of claim 18, wherein subsequent to the restore of the first table, data of the first set of database tables referenced by the first table are accessible in the primary storage subsystem, and the restored first table is available for being browsed or queried using the database application.

20. The system of claim 14, wherein the determination of the first set of database tables and the packaging of the information in the first table metadata index are performed during a backup of data from the primary storage subsystem to the secondary storage subsystem.