Create backups and actually test restoration

The existence of a backup file does not answer the decisive question: can the required content actually be restored and used? Dependable backup practice connects creation, protection and repeated restoration checks. Those steps belong together but provide different evidence. A green backup status cannot replace a tested route back to useful data.

This guide develops that route through an actually executed SQLite example. An original teaching database is backed up and then changed. A separate restoration file is created from the backup, and all teaching records are compared with their original state. No production customer data is used. The experiment checks one database rather than a complete return to business operation.

Begin with the required outcome

Describe what must become available after an outage. A website may depend on a database, uploaded files, configuration and certain keys. An application may have additional services or external dependencies. A list of configured backup jobs answers only part of that question. What matters is whether those jobs together can produce the required state.

Also define the work that should be possible afterwards. A database may open while associated documents are missing. An application may start but be unable to send messages or decrypt files. Such differences explain why the required operational state should be described before individual backup tools are selected.

Separate the data point from recovery duration

The acceptable data-loss question concerns the point to which information must be recovered. The recovery-duration question concerns the time available before the intended availability is needed. These goals are commonly described through recovery point and recovery time objectives. They should fit the actual operation. Frequent backup creation alone says little about restoration duration.

Record targets as requirements and observed outcomes as measurements. Quickly restoring one file does not establish a promised recovery time for an entire application. This teaching example therefore claims no downtime or performance benchmark. Its purpose is an inspectable content comparison after an independent restoration.

Name the backup scope explicitly

This is a working structure rather than a claim that every system has the same components. Add actual dependencies and avoid collecting unnecessary data. Copies still need protection. A backup can contain the same confidential information as the original system and preserve additional historical states.

Distinguish copying from a consistent backup

A live database needs a method appropriate to its actual operating state. An uncoordinated file copy is not automatically consistent. The SQLite lesson uses VACUUM INTO, which SQLite documents as creating a separate file with a consistent logical state. The example executes it through the available PDO connection.

This choice applies to the demonstrated system. It is not a universal procedure for every database product. Other systems have different backup and recovery mechanisms. Consult their documentation and the actual operating mode. A working SQLite extract proves neither the suitability of a casual file copy elsewhere nor correct protection of a distributed application.

Protect against common failure causes

Another file in the same easily modified location may face the same failure as the original. Consider which events could affect both. Technical outages, accidental changes and unauthorised access are examples. Suitable separation follows from the scenario and actual infrastructure rather than the appearance of two different filenames.

Define who can read, modify or delete backups. OWASP recommends appropriate permissions and ideally encryption for database backups. Availability of the necessary keys also needs consideration. An encrypted copy helps recovery only when authorised access to its prerequisites remains possible. Protection should not be planned independently of the recovery task.

Prepare an independent test destination

A restoration exercise should not accidentally overwrite production data. Define the target, environment and expected effect beforehand. This lesson creates only new files in a fresh subdirectory. Existing files are neither replaced nor deleted. The boundary makes the exercise inspectable and separates its effects from the surrounding project.

A real application introduces further concerns. A restored environment could trigger scheduled messages, external integrations or background jobs. Those effects must match the exercise. Starting software is not a sufficient plan by itself. The test area should investigate the intended properties without causing unintended business operations.

The executed PHP experiment

The code creates two original teaching records and generates a SQLite backup. It then changes one title in the source while keeping the same row count. The backup is copied to a new file and opened there. Every identifier, title and body is compared with the expected original values. A database structural check and unchanged backup hash provide additional evidence.

<?php
declare(strict_types=1);
$folder = __DIR__ . '/runs/run-' . gmdate('Ymd-His') . '-' . bin2hex(random_bytes(4));
if (file_exists($folder) || !mkdir($folder, 0700, true)) {
    throw new RuntimeException('Fresh example folder required');
}
$sourceFile = $folder . '/source.sqlite';
$backupFile = $folder . '/backup.sqlite';
$restoreFile = $folder . '/restored.sqlite';
$source = new PDO('sqlite:' . $sourceFile, null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$source->exec('CREATE TABLE notes (id INTEGER PRIMARY KEY, title TEXT NOT NULL, body TEXT NOT NULL)');
$expected = [
    ['id' => 1, 'title' => 'Prüffrage', 'body' => 'Welche Inhalte gehören zum Sicherungsstand?'],
    ['id' => 2, 'title' => 'Prüfgrenze', 'body' => 'Dieses Beispiel prüft nur die eigene SQLite-Datei.'],
];
$insert = $source->prepare('INSERT INTO notes (id, title, body) VALUES (:id, :title, :body)');
foreach ($expected as $row) {
    $insert->execute($row);
}
$source->exec('VACUUM INTO ' . $source->quote($backupFile));
$backupHash = hash_file('sha256', $backupFile);
$source->exec("UPDATE notes SET title = 'Geänderter Ausgangsstand' WHERE id = 1");
$changed = $source->query('SELECT id, title, body FROM notes ORDER BY id')->fetchAll(PDO::FETCH_ASSOC);
if (count($changed) !== count($expected) || $changed === $expected) {
    throw new RuntimeException('Changed fixture required');
}
if (file_exists($restoreFile) || !copy($backupFile, $restoreFile)) {
    throw new RuntimeException('Fresh restore destination required');
}
$restored = new PDO('sqlite:' . $restoreFile, null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$actual = $restored->query('SELECT id, title, body FROM notes ORDER BY id')->fetchAll(PDO::FETCH_ASSOC);
$quickCheck = $restored->query('PRAGMA quick_check')->fetchColumn();
if ($actual !== $expected || $quickCheck !== 'ok' || hash_file('sha256', $backupFile) !== $backupHash) {
    throw new RuntimeException('Restore verification failed');
}
$report = [
    'passed' => true, 'folder' => $folder, 'sqlite_version' => $source->query('SELECT sqlite_version()')->fetchColumn(),
    'expected' => $expected, 'changed_source' => $changed, 'restored' => $actual,
    'quick_check' => $quickCheck, 'backup_sha256' => $backupHash,
    'backup_unchanged' => true, 'row_count_alone_insufficient' => count($changed) === count($actual),
    'scope' => 'Original isolated SQLite fixture, not production data or full application recovery.',
];
file_put_contents($folder . '/restore-report.json', json_encode($report, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE | JSON_THROW_ON_ERROR));
echo json_encode($report, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE | JSON_THROW_ON_ERROR), PHP_EOL;

The experiment needs PHP with PDO SQLite and a SQLite version supporting the selected method. The actual environment reported SQLite 3.53.4. That identifies the test environment rather than a universal minimum version for the article. The fresh directory retains the source, backup, restoration and report. All generated evidence is preserved.

Equal row counts do not establish equal content

The source database contains two rows before and after the change. The restored database also contains two. A count alone therefore misses the important difference. The changed source's first title is Geänderter Ausgangsstand, while the original and restored title is Prüffrage. Comparing all fields exposes the distinction.

The lesson is that evidence should match the property being asserted. A count checks a count. An openable file does not establish every professional relationship. A content comparison can verify particular expected data. None should silently become a claim that the entire business system is operational.

Interpret hashes and database checks accurately

The backup hash is compared before and after the exercise. That checks whether this file remained unchanged during the experiment. It does not by itself establish that the original snapshot was complete or professionally correct. An unchanged incorrect file would remain unchanged too. State the meaning of the comparison explicitly.

PRAGMA quick_check returns ok in the experiment. This database check supplements rather than replaces the record comparison. It does not assess documents outside the file. Other requirements may call for additional checks. The report records what was actually done instead of claiming unrestricted success across every possible property.

Connect the technical route to a useful task

A real application needs test tasks reflecting its purpose. Can a required document be opened? Do important records correspond to their files? Are settings needed for the intended work available? Define those tasks before testing. Otherwise, a visually successful launch can encourage inventing an accommodating success definition afterwards.

Choose coverage appropriate to the scenario. A small sample can establish particular properties without replacing complete examination of all data. Name what was checked and what remains open. Comparing all two records is feasible here. That completeness applies only to the small original teaching table.

Establish expected content beforehand

The code defines its expected records before backing up. Identifiers, titles and bodies are compared completely. In a real application, selecting suitable expectations needs to fit the system. A large dataset does not become fully verified through an arbitrary sample, even when that sample is convenient to display.

Do not accidentally derive the reference from the already modified source. Doing so would treat the very change being investigated as correct. Distinguish the backup point from the reference used for verification. A professional test case should explain which state is being confirmed and why that state matters to the scenario.

Where relevant, test relationships too. A document identifier contributes little if the associated file cannot be read. Fetching one record does not establish that every component is connected. This SQLite experiment has no external files and therefore makes no claim about that wider property.

Do not overstate the file separation

All experiment files intentionally live in one local test directory. Separation prevents overwriting the source and enables comparison. It does not constitute geographically independent storage. Failure of the shared drive could still affect every file there. This is a verification mechanism rather than a complete protection architecture.

A real backup plan must address that additional question separately. Which common failure is considered? Which copy remains reachable? Which authorised person can carry out recovery? The answers depend on the actual environment. A cloud symbol in a diagram cannot establish that separation has been implemented.

The small experiment also does not verify encryption or access control. Its contents are original teaching notes without client secrets or real credentials. Applying a method in a real system requires suitable protection and availability of necessary keys to be planned and checked independently.

Select the appropriate version

The newest state is not always the needed state. If an error predates the latest backup, an older suitable version may matter. Define how snapshots are identified and selected. Name, time and associated system state should align. A collection of similarly named files makes that decision harder during recovery.

Retention should meet the actual requirements. This article prescribes no universal period. Business need, protection requirements and any applicable legal duties must be established for the specific collection. The ability to create many copies does not answer which states should be kept or who decides.

Make failed attempts visible

The code stops if a fresh destination cannot be prepared or expected outcomes are absent. It does not fabricate success. A failed run may still have created files, which are not automatically valid backups. A completed report belongs to its successful run and should not be borrowed from an earlier execution.

In automation, failures need to reach someone who can investigate them. A recurring job offers little assurance when its failure goes unnoticed. Distinguish failed creation, transfer and restoration. Those states can have different causes and require different corrective work.

Define a meaningful timing exercise

If recovery duration matters, define its start and end. Does timing begin at outage recognition or only when copying starts? Does it end when the database opens or after a useful professional task succeeds? Without this definition, similar-looking values may describe different workflows and create unjustified confidence.

Include necessary manual steps. Accessing storage, choosing the correct snapshot and preparing the environment may belong to recovery. Program runtime alone omits those tasks. The published experiment contains no such recovery-duration measurement, so its database results must not become a promised operational time.

Keep a usable verification report

Connect backup state, target environment, performed steps and observed outcomes. Include versions, relevant prerequisites and unresolved points. Refer to actual files or logs. Restoration successful is too vague if later readers cannot determine which contents or functions were examined.

Keep backup-job status separate from restoration-test status. The former can report that a procedure completed. The latter describes a return route and the properties checked along it. Both are useful. Distinguishing them supports investigation when a later exercise produces a different outcome.

Use changes as review triggers

A passing test remains evidence of its particular state. Changes to database version, tool, destination or application may require another check. New file types and dependencies can alter scope too. Maintain the plan alongside the actual system rather than treating an old report as indefinitely current evidence.

Assign responsibility and useful review triggers. Frequency follows need and risk rather than one universal interval. What matters is that planned tests occur and findings are addressed. A failed exercise is concrete work. It should not disappear beneath an unchanged green overall status.

Questions for a practical review

Is opening the backup enough? It is useful evidence, but it does not establish the required contents. This exercise therefore reads all teaching records and compares their fields. A production application adds appropriate professional tasks. State which property has been examined and which still needs attention.

Does a matching hash establish completeness? It establishes unchanged bytes in the comparison described here. Whether the correct data was originally included is a separate question. Keeping those statements apart prevents integrity checking from becoming an unsupported claim of professional acceptance.

What follows a failed attempt? Record the observed deviation and the stage affected. An unreadable file calls for a different investigation from a missing associated document. Address the cause and repeat the appropriate route. Success of another snapshot should not conceal the current failure. A useful report narrows the next corrective action.

How does the exercise become repeatable? Describe prerequisites, destination, expected contents and steps clearly enough for another authorised person to follow. Identify required tools and the current instruction. When the system changes, verify those assumptions again. Repeatability comes from explicit conditions and actual execution rather than copying an earlier success message.

Should a test use real data? The necessary evidence determines the appropriate material and safeguards. This lesson uses only original generated notes, so no private client information is involved. A real recovery exercise may require representative content under suitable controls. Do not assume that a disposable teaching fixture establishes every property of that different environment.

Who accepts the outcome? Define responsibility before the exercise. Technical verification and confirmation of a useful business task may involve different people. Neither should be invented after the fact merely to obtain a positive status. Keep unresolved findings visible and assign the work needed to address them.

Checklist for a bounded restoration test

• The required state and considered failure scenario are explicit.
• Backup scope and dependencies are traceable.
• The method fits the actual database and operating mode.
• Backups and necessary keys are appropriately protected and available.
• The destination is separate and avoids accidental overwriting.
• Expected content and professional test tasks are defined beforehand.
• Counts, hashes and contents are interpreted according to what they establish.
• Versions, results, limitations and unresolved matters are recorded.
• Someone owns remediation and subsequent testing.

Publishing the teaching code makes the experiment inspectable. It does not replace an operating instruction tailored to another system. Transfer the verification principle first: identify the expected state, restore into a separate destination and check appropriate content. Choose the actual method and additional protections from the real requirements afterwards.

Sources and review date

Sources were checked on 9 November 2026. SQLite documents its backup API and alternatives, VACUUM with INTO and quick_check. OWASP discusses backup protection in its database-security guide. NIST defines Recovery Point Objective and Recovery Time Objective. Records and the executed experiment are original teaching material.