Incremental SQL Server Backup via Snapshot Extent Mapping
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
SQL Server databases lack support for incremental backups, which are essential for reducing backup size and minimizing downtime, as they require restoring differential or log backups to a live database instance, leading to increased resource usage and disruption.
Innovation Solution
Implement a method that performs incremental backups by taking snapshots of the database, retrieving and storing changed extents, and converting full backups into incremental files, allowing for efficient storage and restoration without requiring SQL Server assistance, enabling continuous backup operations and reduced storage utilization.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If differential or log backups are restored to a live database instance, then backup and restore operations can be performed, but resource usage increases and database disruption occurs
Solution Approach 1:
The patent segments the backup process into two independent parts: (1) creating a snapshot of the live database that captures the state without requiring the database to be offline, and (2) performing the actual backup operations on the snapshot copy. This allows the live database to remain accessible while backup operations are performed on the snapshot, eliminating the contradiction between backup capability and database availability.
2Reliability
If full backups are performed regularly, then complete database copies are available, but storage utilization increases and backup time increases
Solution Approach 1:
The patent performs a preliminary action by creating a snapshot of the database before performing backup operations. This snapshot captures the database state at a specific point in time, allowing subsequent incremental or differential backups to only capture changes since that snapshot was created, rather than requiring complete full backups each time.
Solution Approach 2:
Instead of performing complete full backups every time, the patent uses partial action by backing up only the portions of the database that have changed since the last snapshot or backup operation. This is achieved through differential backups that leverage the snapshot to identify and backup only modified data pages.
3Productivity
If snapshots are used to minimize SQL server resource usage during backup, then database availability is maintained, but incremental backup functionality is not natively supported
Solution Approach 1:
The patent introduces an intermediary component that sits between the SQL Server snapshot mechanism and the backup storage. This intermediary manages the snapshot creation, tracks changes using allocation maps and differential change maps, and performs incremental backup operations to storage devices. This intermediary layer enables incremental backup functionality without requiring changes to the core SQL Server system, thus maintaining database availability while adding the needed complexity only where required.
Data Source
AI summary
A system, method, and medium for performing incremental backups of a Microsoft SQL server database. A snapshot of the database is created, and then a map identifying the changed extents is retrieved from the snapshot. The changed extents are then retrieved from the snapshot and stored in a backup storage device. For a restore operation, a full database backup file is written to a storage device and then the changed extents from a stored incremental backup file may be merged with the full backup file. Next, the database server is notified of the reconstructed file and then the reconstructed file is mounted by the database server as a live instance of the database.