Backup and restore of databases

• US 9,535,932 B1
• Filed: 03/15/2013
• Issued: 01/03/2017
• Est. Priority Date: 06/29/2012
• Status: Active Grant

• Current Assignee: Paraccel Incorporated (Actian Corporation)
• Original Assignee: Paraccel Incorporated (Actian Corporation)
• Inventors: DeSantis, Robert P., Radanovic, Velimir, Wilhite, David Jr.
• Primary Examiner(s): Stevens, Robert
• Application Number:
  US13/841,602
• Time in Patent Office: 1,390 Days
• Field of Search: 707/654
• US Class Current: 1/1
• CPC Class Codes: G06F 16/21 Design, administration or maintenance of databases

Abstract

A logical backup of a database is performed that allows physical properties of the database to be preserved upon restore. The physical properties of the database include properties related to storage of the data on the hard disk. Preserving the physical properties of a database allows performance debugging of the source database by performing performance tests on the target database rather than a production system. Incremental backup of metadata of a database is performed to minimize execution of statements that provide the final configuration of the database. Incremental backup is performed by restoring a database using a full backup and applying incremental backups to reach a particular state. The user can inspect the restored database after applying each incremental backup to determine whether the database has reached a desired state.

Claims

1. A computer-implemented method comprising:

maintaining a source database storing data as tables, the tables including at least a particular table;

receiving a database query associated with a performance issue on the source database, the database query processing data of the particular table;

creating a backup repository for the source database, the creating comprising;

retrieving data of one or more tables of the source database by executing one or more database queries, the one or more tables including the particular table, wherein execution of the one or more database queries retrieves rows of the one or more tables including rows marked as deleted in the source database, wherein the rows marked as deleted were deleted in the source database before creating the backup repository, and

storing the retrieved data in a backup repository;

restoring a target database using the backup repository, the restored target database storing the particular table including rows of the particular table marked as deleted in the source database; and

executing the database query on the target database to analyze the performance issue of the database query on the source database, the executing comprising processing the restored particular table, wherein the restored particular table stores rows marked as deleted in the source database.

2. The computer-implemented method of claim 1, further comprising:

retrieving from the source database, statistics describing the one or more tables of the source database; and

wherein the target database uses the retrieved statistics for optimizing queries executed by the target database.

3. The computer-implemented method of claim 2, wherein the retrieved statistics comprises sizes of tables.

4. The computer-implemented method of claim 2, wherein the retrieved statistics comprises distribution of values of one or more columns of a table of the database.

5. The computer-implemented method of claim 1, wherein the source database and the target database are stored on a plurality of computers and each row of each table is associated with a location indicating the computer on which the row is stored, the method further comprising:

retrieving additional information from the database, the additional information comprising data describing each table including location information associated with rows of the table; and

restoring a target database comprises storing the retrieved data for each table by storing each row in a location of the target database that maps to a corresponding location of the row in the source database.

6. The computer-implemented method of claim 1, wherein a table of the source database comprises a first set of rows storing data in sorted order based on at least one key and a second set of rows stored in an unsorted order with respect to the at least one key, wherein restoring a target database comprises storing the retrieved data for each table maintaining the sort order of the first set and the unsorted order of the second set.

7. The computer-implemented method of claim 1, further comprising:

retrieving additional information describing a particular order in which rows of the one or more tables are stored in the source database and the restoring comprises storing the rows of the one or more tables in the target database in the particular order.

8. The computer-implemented method of claim 1, wherein the source database is processed using a system having a first hardware configuration and the target database processed using a system having a second hardware configuration distinct from the first hardware configuration.

9. The computer-implemented method of claim 1, wherein each of the one or more tables is associated with physical properties determining a physical layout of data stored on a persistent storage device of the source database, wherein restoring the target database using the backup repository such that the physical layout of the one or more tables in a persistent storage device of the target database matches the physical layout of the one or more tables in the persistent storage device of the source database.

10. A non-transitory computer readable storage medium storing instructions for:

maintaining a source database storing data as tables, the tables including at least a particular table;

receiving a database query associated with a performance issue on the source database, the database query processing data of the particular table;

creating a backup repository for the source database, the creating comprising;

retrieving data of one or more tables of the source database by executing one or more database queries, the one or more tables including the particular table, wherein execution of the one or more database queries retrieves rows of the one or more tables including rows marked as deleted in the source database, wherein the rows marked as deleted were deleted in the source database before creating the backup repository, and

storing the retrieved data in a backup repository;

restoring a target database using the backup repository, the restored target database storing the particular table including rows of the particular table marked as deleted in the source database; and

executing the database query on the target database to analyze the performance issue of the database query on the source database, the executing comprising processing the restored particular table, wherein the restored particular table stores rows marked as deleted in the source database.

11. The non-transitory computer readable storage medium of claim 10, further storing instructions for:

retrieving from the source database, statistics describing the one or more tables of the source database; and

wherein the target database uses the retrieved statistics for optimizing queries executed by the target database.

12. The non-transitory computer readable storage medium of claim 11, wherein the retrieved statistics comprises sizes of tables.

13. The non-transitory computer readable storage medium of claim 11, wherein the retrieved statistics comprises distribution of values of one or more columns of a table of the database.

14. The non-transitory computer readable storage medium of claim 10, wherein the source database and the target database are stored on a plurality of computers and each row of each table is associated with a location indicating the computer on which the row is stored, further storing instructions for:

retrieving additional information from the database, the additional information comprising data describing each table including location information associated with rows of the table; and

restoring a target database comprises storing the retrieved data for each table by storing each row in a location of the target database that maps to a corresponding location of the row in the source database.

15. The non-transitory computer readable storage medium of claim 10, wherein a table of the source database comprises a first set of rows storing data in sorted order based on at least one key and a second set of rows stored in an unsorted order with respect to the at least one key, wherein restoring a target database comprises storing the retrieved data for each table maintaining the sort order of the first set and the unsorted order of the second set.

16. The non-transitory computer readable storage medium of claim 10, wherein each of the one or more tables is associated with physical properties determining a physical layout of data stored on a persistent storage device of the source database, wherein restoring the target database using the backup repository such that the physical layout of the one or more tables in a persistent storage device of the target database matches the physical layout of the one or more tables in the persistent storage device of the source database.

17. A computer-implemented system comprising:

a computer processor; and

a non-transitory computer-readable storage medium storing instructions for;

maintaining a source database storing data as tables, the tables including at least a particular table;

receiving a database query associated with a performance issue on the source database, the database query processing data of the particular table;

creating a backup repository for the source database, the creating comprising;

retrieving data of one or more tables of the source database by executing one or more database queries, the one or more tables including the particular table, wherein execution of the one or more database queries retrieves rows of the one or more tables including rows marked as deleted in the source database, wherein the rows marked as deleted were deleted in the source database before creating the backup repository, and

storing the retrieved data in a backup repository;

restoring a target database using the backup repository, the restored target database storing the particular table including rows of the particular table marked as deleted in the source database; and

executing the database query on the target database to analyze the performance issue of the database query on the source database, the executing comprising processing the restored particular table, wherein the restored particular table stores rows marked as deleted in the source database.

18. The computer-implemented system of claim 17,

wherein the non-transitory computer-readable storage medium further stores instructions for:

retrieving from the source database, statistics describing the one or more tables of the source database, and

wherein the target database uses the retrieved statistics for optimizing queries executed by the target database.

19. The computer-implemented system of claim 17, wherein the source database and the target database are stored on a plurality of computers and each row of each table is associated with a location indicating the computer on which the row is stored, wherein the non-transitory computer-readable storage medium further stores instructions for:

retrieving additional information from the database, the additional information comprising data describing each table including location information associated with rows of the table; and

restoring a target database comprises storing the retrieved data for each table by storing each row in a location of the target database that maps to a corresponding location of the row in the source database.