LEARN. PRACTICE. GROW.Active state can extend beyond one file
The fixture creates a SQLite application with one batch and integer-valued entries. Its initial total is 100, represented by sequence 1. It enables WAL, disables automatic checkpointing only in this temporary area, and explicitly checkpoints the initial database. It then commits an entry of 25 and updates the total to 125. The runner observes an unchanged main-file hash and content present in WAL. Copying only the main file produces a readable database still containing sequence 1. This experiment does not rely on random corruption or physical failure. It shows that a running database's committed state can span components that a simple file-copy command does not interpret. File existence and successful opening provide insufficient evidence to conclude freshness. The required recovery point must be assessed separately.
Copy through the supported mechanism
The second copy uses Python's Connection.backup interface to the SQLite backup API. The source remains open, but the runner introduces no concurrent writer during that call. The copy includes sequence 2 and total 125. The destination is closed and prepared as a standalone artifact before probing. In a separate experiment, the writer connection changes the total to 999 without committing; another connection performs backup and still obtains 125. The pending change is rolled back at the end of that experiment. This distinction teaches observation of committed state without turning the experiment into a concurrency benchmark. The API supports incremental procedures, but performance under load, lock errors, and retry policy require their own experiments. Record completion, errors, and artifact criteria instead of accepting mere artifact presence.
Distinguish consistency from freshness
After backup, the source commits another 75, reaching sequence 3 and total 200. The earlier artifact retains sequence 2 and total 125. Its values remain internally coherent, but it fails when the probe requires sequence 3. A consistent point can be too old for the recovery objective. In the fixture, the requirement is a sequence supplied by the experiment rather than calculated from the copy itself. In a real system, agree on the reference, its producer, and its relationship to operational cut-off. Without timestamps and a temporal definition, the difference between sequences cannot establish how many minutes of data were lost. Similarly, a quick query does not measure complete RTO. Communicate the available point, required point, and observed gap separately.
Apply the reasoning to operations
In a fictional funds scenario, the infrastructure owner delivers the artifact and the application owner confirms whether it contains required operations. The procedure should specify writer state, supported method, location, dependencies, and rejection criteria. Do not manually delete WAL to reduce backup size or combine live files copied at different times as if that established consistency. WAL also has coordination requirements: SQLite documentation restricts its use to processes on the same host and does not support the usual multiple-host network-share design. This behavior belongs to SQLite; it is not an Oracle, DB2, or universal storage procedure. The complete code below uses only disposable files on Darwin. It neither formats volumes nor changes mounts, issues device commands, or accesses a production database.
#!/usr/bin/env python3
"""Original bounded SQLite backup/restore lab. Disposable local files only; no production database."""
import argparse,hashlib,json,platform,shutil,sqlite3,subprocess,sys,tempfile
from pathlib import Path
def probe(file,required):
db=sqlite3.connect(Path(file).resolve.as_uri+'?mode=ro',uri=True)
try:
integrity=[x[0]for x in db.execute('PRAGMA integrity_check')]
foreign=list(map(list,db.execute('PRAGMA foreign_key_check')))
version=db.execute('PRAGMA user_version').fetchone[0]
rows=list(map(list,db.execute('SELECT id,batch_id,amount FROM entries ORDER BY id')))
declared=db.execute('SELECT declared_total FROM batches WHERE id=1').fetchone[0]
actual=sum(x[2]for x in rows if x[1]==1);latest=max((x[0]for x in rows),default=0)
conditions=dict(structural=integrity==['ok'],foreignKeys=not foreign,supportedVersion=version==1,totals=actual==declared,freshness=latest>=required)
return dict(ready=all(conditions.values),conditions=conditions,integrity=integrity,foreignKeyFailures=foreign,schemaVersion=version,rows=rows,declaredTotal=declared,actualTotal=actual,latestSequence=latest,requiredSequence=required)
finally:db.close
def main(output):
checks=[];observations={};calls=[]
def check(name,condition):assert condition,name;checks.append(name)
def digest(file):return hashlib.sha256(Path(file).read_bytes).hexdigest
def inspect(label,file,required):
result=subprocess.run([sys.executable,str(Path(__file__).resolve),'--probe',str(file),'--required',str(required)],capture_output=True,text=True,timeout=15)
calls.append(dict(label=label,exit=result.returncode,stdout=result.stdout,stderr=result.stderr))
check(label+': probe exited successfully',result.returncode==0)
return json.loads(result.stdout)
def backup(source,destination):
target=sqlite3.connect(destination)
try:source.backup(target);target.execute('PRAGMA journal_mode=DELETE').fetchone
finally:target.close
with tempfile.TemporaryDirectory(prefix='dr-storage-recovery-')as temporary:
root=Path(temporary);live=root/'live.sqlite'db=sqlite3.connect(live,isolation_level=None)
try:
check('WAL mode enabled',db.execute('PRAGMA journal_mode=WAL').fetchone[0]=='wal')
db.execute('PRAGMA wal_autocheckpoint=0');db.execute('PRAGMA foreign_keys=ON')
db.executescript('''BEGIN; PRAGMA user_version=1;
CREATE TABLE batches(id INTEGER PRIMARY KEY,declared_total INTEGER NOT NULL);
CREATE TABLE entries(id INTEGER PRIMARY KEY,batch_id INTEGER NOT NULL REFERENCES batches(id),amount INTEGER NOT NULL);
INSERT INTO batches VALUES(1,100);INSERT INTO entries VALUES(1,1,100);COMMIT;''')
checkpoint=db.execute('PRAGMA wal_checkpoint(TRUNCATE)').fetchone;check('baseline checkpoint completed',checkpoint[0]==0)
baselineHash=digest(live)
db.executescript('BEGIN;INSERT INTO entries VALUES(2,1,25);UPDATE batches SET declared_total=125 WHERE id=1;COMMIT;')
wal=Path(str(live)+'-wal');check('committed change remains in WAL',wal.existsand wal.stat.st_size>0 and digest(live)==baselineHash)
raw=root/'main-only.sqlite'shutil.copyfile(live,raw);stale=inspect('main-only-copy',raw,2)
check('main-only copy structurally valid',stale['conditions']['structural'])
check('main-only copy misses committed sequence',stale['latestSequence']==1 and not stale['ready']and not stale['conditions']['freshness'])
observations['mainOnly']=dict(result=stale,mainBytesUnchanged=True,walBytes=wal.stat.st_size,sourceConnectionOpen=True)
saved=root/'online.sqlite'backup(db,saved);snapshot=inspect('online-backup',saved,2)
check('online backup includes committed WAL content',snapshot['ready']and snapshot['latestSequence']==2 and snapshot['actualTotal']==125)
observations['onlineBackup']=snapshot
db.execute('BEGIN IMMEDIATE');db.execute('UPDATE batches SET declared_total=999 WHERE id=1')
reader=sqlite3.connect(live)
try:uncommitted=root/'uncommitted-excluded.sqlite'backup(reader,uncommitted)
finally:reader.close;db.execute('ROLLBACK')
excluded=inspect('uncommitted-excluded',uncommitted,2)
check('separate backup excludes uncommitted update',excluded['ready']and excluded['declaredTotal']==125)
observations['uncommittedExcluded']=excluded
db.executescript('BEGIN;INSERT INTO entries VALUES(3,1,75);UPDATE batches SET declared_total=200 WHERE id=1;COMMIT;')
older=inspect('snapshot-after-new-commit',saved,3)
check('snapshot remains at earlier generation',older['latestSequence']==2 and older['conditions']['totals']and not older['conditions']['freshness'])
observations['laterCommit']=dict(snapshot=older,liveLatest=db.execute('SELECT max(id) FROM entries').fetchone[0],liveTotal=db.execute('SELECT declared_total FROM batches').fetchone[0])
db.close;live.unlink;check('synthetic original removed before restore',not live.exists)
restored=root/'restored.sqlite'shutil.copyfile(saved,restored);before=digest(restored);accepted=inspect('restored-application',restored,2);after=digest(restored)
check('restored application passes explicit contract',accepted['ready']and accepted['actualTotal']==125)
check('read-only restore probe preserves artifact',before==after)
observations['restored']=dict(result=accepted,hashBefore=before,hashAfter=after,originalLiveNotRequired=True)
broken=root/'business-defect.sqlite'shutil.copyfile(saved,broken);edit=sqlite3.connect(broken)
try:edit.execute('UPDATE batches SET declared_total=999 WHERE id=1');edit.commit
finally:edit.close
bad=inspect('business-defect',broken,2)
check('integrity check misses business-total defect',bad['conditions']['structural']and bad['conditions']['foreignKeys']and not bad['conditions']['totals']and not bad['ready'])
observations['businessDefect']=bad
orphan=root/'foreign-key-defect.sqlite'shutil.copyfile(saved,orphan);edit=sqlite3.connect(orphan)
try:edit.execute('PRAGMA foreign_keys=OFF');edit.execute('INSERT INTO entries VALUES(3,999,10)');edit.commit
finally:edit.close
fk=inspect('foreign-key-defect',orphan,2)
check('foreign_key_check catches defect beyond integrity_check',fk['conditions']['structural']and not fk['conditions']['foreignKeys']and not fk['ready'])
observations['foreignKeyDefect']=fk
incompatible=root/'unsupported-version.sqlite'shutil.copyfile(saved,incompatible);edit=sqlite3.connect(incompatible)
try:edit.execute('PRAGMA user_version=2');edit.commit
finally:edit.close
unsupported=inspect('unsupported-version',incompatible,2)
check('application rejects unsupported schema marker',unsupported['conditions']['structural']and unsupported['conditions']['totals']and not unsupported['conditions']['supportedVersion']and not unsupported['ready'])
observations['unsupportedVersion']=unsupported
missing=root/'missing.sqlite'
try:probe(missing,2)
except sqlite3.OperationalError:rejected=True
else:rejected=False
check('read-only missing path fails without creation',rejected and not missing.exists)
observations['missingPath']=dict(openRejected=rejected,fileCreated=missing.exists)
finally:db.close
check('temporary database artifacts removed',not root.exists)
report=dict(python=platform.python_version,sqlite=sqlite3.sqlite_version,system=platform.system,release=platform.release,machine=platform.machine,runnerSha256=hashlib.sha256(Path(__file__).read_bytes).hexdigest,checks=checks,observations=observations,probeProcesses=calls,scope='Original local SQLite fixture on the recorded host. Actual WAL copy, backup API, isolated restore probes and deliberately introduced defects. Sequence thresholds are synthetic acceptance criteria, not measured RPO/RTO. No external service, Oracle/DB2 behavior, Linux execution, network filesystem, power failure, device change, load benchmark, encryption-key recovery or production data.')
if output:Path(output).write_text(json.dumps(report,indent=2)+'\n')
print(json.dumps(dict(groups=len(observations),checks=len(checks),probeProcesses=len(calls),sqlite=sqlite3.sqlite_version,output=output)))
if __name__=='__main__':
p=argparse.ArgumentParser;p.add_argument('--output');p.add_argument('--probe');p.add_argument('--required',type=int,default=2);a=p.parse_args
if a.probe:print(json.dumps(probe(a.probe,a.required)))
else:main(a.output)
Tejo copies the main file during activity. The copy opens but misses an already committed operation; supported backup recovers the required sequence.
Common pitfalls
Treating a copied file as the whole active database, deleting WAL, accepting by size, or confusing a sequence with measured minutes of loss.
Related topics: Snapshots and recovery · Transactions and dependencies
An artifact can be structurally valid and stale. Supported copying and recovery-point validation answer different questions.
Reference: SQLite online backup API · BigSavant Storage 2026-09; selected Linux and AWS storage behavior