Database Point-in-Time Recovery After Transaction Log Model Switch

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

When a database's transaction log fills its storage space, temporarily switching from the full recovery model to the simple recovery model to address storage issues disrupts point-in-time recovery capabilities due to transaction log sequence number resets, causing gaps in transaction log information and sequence numbers, which leads to failed recovery processes.

Innovation Solution

A system identifies the most recent backup information and current transaction log sequence number to create an unscheduled full backup of the database, followed by an independent backup of the transaction log, ensuring continuous point-in-time recovery by eliminating gaps in transaction log information and sequence numbers.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Quantity of substance

If the database switches from full recovery model to simple recovery model to address transaction log storage space, then storage space is freed, but point-in-time recovery capability is lost due to sequence number resets

Engineering Contradiction:

Improvetransaction log storage spaceVSAvoidpoint-in-time recovery capability

Core Design Contradiction:

Quantity of substanceVSReliability

Solution Approach 1:

The system performs a full database backup before switching to simple recovery model. This preliminary backup captures the database state with complete transaction log sequence numbers, enabling point-in-time recovery to be performed on this backup copy even after the live database switches to simple recovery model and loses sequence number continuity.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The solution separates the database into two states: the live database operating in simple recovery model for storage efficiency, and a backup copy maintained in full recovery model for recovery capability. This segmentation allows each instance to serve its specific purpose without compromising the other.

Inventive Principle:

Principle #1Segmentation

2Reliability

If the database remains in full recovery model to maintain point-in-time recovery capability, then recovery reliability is preserved, but transaction log storage space is consumed

Engineering Contradiction:

Improvepoint-in-time recovery capabilityVSAvoidtransaction log storage space

Core Design Contradiction:

ReliabilityVSQuantity of substance

Solution Approach 1:

The system proactively creates a full database backup before the transaction log fills up, capturing the complete state including sequence numbers. This preliminary action ensures recovery capability is preserved in the backup without requiring the live database to maintain full recovery model indefinitely.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The system changes the recovery model parameter from full to simple, and simultaneously changes the backup strategy parameter to create unscheduled full backups. This parameter transformation allows the database to operate in a space-efficient mode while periodic parameter changes (backups) preserve recovery capability.

Inventive Principle:

Principle #35Parameter changes

3Reliability

If the system creates unscheduled full backups to enable point-in-time recovery after switching to simple recovery model, then recovery capability is restored, but backup operation complexity increases

Engineering Contradiction:

Improvepoint-in-time recovery capabilityVSAvoidbackup operation complexity

Core Design Contradiction:

ReliabilityVSDevice complexity

Solution Approach 1:

The backup/restore application automatically detects when a database has switched to simple recovery model and autonomously creates unscheduled full backups without requiring manual intervention. This self-service mechanism reduces operational complexity by automating the detection and response process.

Inventive Principle:

Principle #25Self-service

Solution Approach 2:

The system implements feedback by monitoring the recovery model status of databases. When the backup/restore application detects a change to simple recovery model, it triggers the unscheduled backup process. This feedback loop ensures recovery capability is maintained without requiring complex manual configuration.

Inventive Principle:

Principle #23Feedback

Data Source

PatentUS11403192B1Enabling point-in-time recovery for databases that change transaction log recovery models

Publication Date: 2022.08.02 EMC IP HLDG CO LLC

AI summary

Enabling point-in-time recovery for databases which change transaction log recovery models is described. If a database is associated with a recovery model that creates independent backups of the database's transaction log, a system identifies most recent backup information and a current transaction log sequence number. If the most recent backup information includes a transaction log sequence number and the current transaction log sequence number is a reset value, the system creates a full backup of the database, without first creating any independent backup of the transaction log. The system creates an independent backup of the transaction log when the independent backup of the transaction log is scheduled next, subsequent to creating the full backup of the database.