Database Backup and Recovery

6.3 Database Backup and Recovery

6.3.1 Scope and Recovery Objectives

Back up schema, business data, relationships, and required objects. Include binary photo tables, coordinate external photo backups, and arrange recovery for external services.

The following is an actionable suggested baseline, not an automatic CREVIA service or recovery guarantee. Implement it through Windows facilities, database jobs, or consistent offline copying.

Daily backups can lose a day's work. If unacceptable, increase frequency, for example every four hours, and prepare native incremental/log chains for finer recovery. A four-hour return-to-basic-production objective is an exercise target requiring measurement, not a promise based on copy speed.

Overwriting one filename cannot recover errors discovered days later. Same-disk copies do not protect against disk failure. Keep production, an independent backup, and an offsite/offline copy without exposing all to the same always-writable account/share. Exports, screenshots, and Save As are not full database backups.

6.3.2 File Database Backups

Arrange a shutdown window. Notify every writer, stop entry/import/automatic work, save and exit CREVIA and other database applications, confirm no background writers, and copy engine-required data plus companion state into a new dated directory. Coordinate external photos/resources and validate an isolated copy before resuming.

SQLite

Active WAL state can hold changes absent from the main file. For online work, use a supported engine backup tool, not arbitrary file combinations or deletion. For this offline example, copy the entire Database directory after stopping all users and copy Photos/layout in the same window. Preserve any same-batch StaffCard.db-wal state; never mix generations.

Copy again to D:\CREVIARestore\StaffCard\Database, open that recovery copy in a compatible SQLite tool, and run separately:

PRAGMA quick_check;
PRAGMA foreign_key_check;

Quick check should return ok; a clean foreign-key check returns no violation rows. Preserve failures and choose another verified backup rather than repairing the only copy. Engine checks do not detect every photo mismatch or duplicate issuance; verify business samples in COC too. This ordinary-workstation procedure does not ask operators to merge or clean production WAL state.

Access

Close CREVIA, Access, and all other connections, then copy .accdb or .mdb. For split deployments, inventory front ends and all linked back ends; the front end alone may not contain personnel.

.laccdb/.ldb are lock files, not data backups. Investigate remaining connections rather than deleting locks as a shutdown method. Run Compact and Repair only on a recovery copy with another safe backup. Verify key tables, photos, relationships, and linked targets, ensuring they do not point back to production.

6.3.3 Server Database Backups

Use the engine's official administrator tools. Client installation folders do not contain server personnel data. Include schema/data and required logs/objects, verify jobs/readability/retention, and inventory server logins, roles, settings, and external storage separately. Coordinate photos. Point-in-time recovery needs an established chain before failure; keys for encrypted backups must also be safely retained.

Commands below run in maintenance tools, not inside CREVIA. staffcard, staffcard_restore, db-demo, and db-restore-demo are fictitious. Use authorized accounts and never place plaintext passwords on command lines.

SQL Server

1. In SQL Server Management Studio connect to the correct instance and verify staffcard and its business tables.
2. Choose Tasks > Back Up, Full, Database. A copy-only maintenance backup can avoid changing the differential baseline.
3. Use a distinct destination such as E:\SQLBackups\staffcard_20261015_1800.bak and remove unwanted defaults. This path is on the SQL Server host, whose service account needs write access.
4. Enable verification on completion and confirm success. Do not append unclear backup sets to the only old copy or overwrite all history.
5. Restore from the new device/file to staffcard_restore.
6. Check new data/log physical paths and names. Do not overwrite production files or use WITH REPLACE for the exercise.
7. Connect a test layout to the restored database and verify records/photos. Encrypted backups or TDE require the corresponding securely backed-up certificate/key.

Database backup does not automatically include server-level logins. Repair mappings/permissions appropriately rather than granting ordinary accounts server-administrator rights.

MySQL

Use a server-compatible client and create the backup directory first. --password prompts locally; --result-file avoids PowerShell redirection encoding changes.

mysqldump --host=db-demo --user=crevia_backup --password --single-transaction --routines --events --triggers --hex-blob --default-character-set=utf8mb4 --result-file="E:\CREVIABackup\StaffCard\2026-10-15_1800\staffcard.sql" staffcard

This assumes InnoDB and no concurrent schema changes. Single-transaction does not snapshot nontransactional MyISAM tables; stop writes/use the appropriate locking plan there. Verify privileges for routines/events/views/tables. A partial file after permission errors is not success.

Immediately check $LASTEXITCODE is 0, errors, and a nonempty file. Restore on an isolated db-restore-demo with its event scheduler disabled and external effects isolated; do not disable production scheduling for this test. Create staffcard_restore with the original charset/collation, then import:

mysql --host=db-restore-demo --user=crevia_restore --password --default-character-set=utf8mb4 staffcard_restore --execute="source E:/CREVIABackup/StaffCard/2026-10-15_1800/staffcard.sql"

The dump intentionally omits --databases to avoid creating/switching to the original name. Still inspect USE, events, routines, cross-database references, DEFINER, and permissions. Stop on errors; partial tables do not establish a completed restoration.

PostgreSQL

Use compatible PostgreSQL maintenance clients:

pg_dump --host=db-demo --port=5432 --username=crevia_backup --password --format=custom --file="E:\CREVIABackup\StaffCard\2026-10-15_1800\staffcard.dump" staffcard

Immediately check $LASTEXITCODE and list the archive:

pg_restore --list "E:\CREVIABackup\StaffCard\2026-10-15_1800\staffcard.dump"

A readable archive list is not a restore test. Create an empty staffcard_restore on an isolated host with matching encoding, locale, and extensions, then run:

pg_restore --host=db-restore-demo --port=5432 --username=crevia_restore --password --dbname=staffcard_restore --no-owner --no-privileges --exit-on-error "E:\CREVIABackup\StaffCard\2026-10-15_1800\staffcard.dump"

Those ownership/privilege options require assigning proper test-user permissions afterward. Check sequences, extensions, roles, and services. A single-database pg_dump does not back up cluster roles/tablespaces. Prefer the same server major version; do not assume downgrade recovery to an older major release.

6.3.4 Isolated Recovery

Preserve the failed database and determine scope/time before cleanup. Restore a verified matching database/photo batch to a new file path, instance, or database name. In a copied test layout, point to that target, disable automatic output, and isolate production-writing interfaces/triggers. Verify identity, structure, relationships, photos, query results, and role scope.

After approval, stop old connections and switch production consistently during maintenance. Older data can revert print states and numbering: reconcile already-issued physical cards and ledgers before enabling production to prevent reissuance or duplicate authorization.

6.3.5 Recovery Acceptance and Reopening

Record backup ID, target, downtime, results, and approver, and retain a pre-switch snapshot. Keep automatic/unattended tasks off until all stations are switched and verified.