Database Backup Data Loading Tool for Subset Extraction
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing solutions for extracting a logical subset of data from a production database for development, testing, or reporting are impractical due to large file sizes and disk storage limitations, making it difficult to achieve a specific small subset of data desired by users.
Innovation Solution
A method and apparatus for loading data from a database backup image that selects a subset statement, identifies tables based on metadata, constructs table statements, and unloads selected data into a target database using filters, allowing for a granular extraction of database rows and preserving referential integrity.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If a full database restore is performed on the target system, then complete data availability is achieved, but disk storage requirements exceed the capacity of smaller target test systems
Solution Approach 1:
The patent extracts only the specific subset of data needed for the target system by using SQL query statements to identify and extract particular tables and rows from the production database backup, rather than restoring the entire database. This extraction process retrieves only the necessary data portion while leaving unnecessary data behind, directly resolving the storage capacity contradiction.
Solution Approach 2:
The patent segments the database restoration process into distinct phases: first creating the database structure without data, then selectively loading only specific tables and rows using generated SQL statements. This segmentation allows the target system to receive only the required data subset, matching its smaller storage capacity while maintaining data completeness for the needed subset.
2Ease of operation
If select queries with join operators are run against the production database, then a logical subset of data can be extracted, but the challenging workload requirements make this impractical
Solution Approach 1:
The patent performs preliminary actions by pre-generating optimized SQL query statements that encapsulate the complex join operations and extraction logic before the actual data extraction process. These pre-generated statements are stored and reused, eliminating the need to execute complex queries repeatedly against the production database during normal operations, thus reducing operational complexity while maintaining extraction capability.
3Adaptability or versatility
If the smallest logical subset extractable is at the file level, then backup structure simplicity is maintained, but the granularity is too coarse for specific small logical subsets desired by users
Solution Approach 1:
The patent introduces dynamic adaptability by allowing users to specify flexible query criteria for data extraction at the row level within tables, rather than being constrained to fixed file-level boundaries. The system dynamically generates SQL statements based on user-specified conditions, enabling extraction of any logical subset size and composition while maintaining a relatively simple overall process through automated statement generation.
Data Source
AI summary
In an exemplary embodiment of this disclosure, a method for loading data from a backup image of a database includes selecting a subset statement defining a subset of the data in the database. Tables of the database are identified based on metadata of the database. A target database is written having the structure but not the data of the identified tables. One or more table statements are constructed, by a computer processor, defining a subset of each identified table based on the subset statement. Selected data is unloaded from a backup image into the target database using respective table statements as filters.