SQLiteのバックアップはファイルコピーでは壊れる：WALモードで実測した安全な方法

はじめに

SQLiteは「1ファイルのDB」として手軽だが、WALモードで運用していると、素朴なファイルコピーは安全なバックアップにならない。バックアップ目的で .db ファイルをコピーしたのに、コピー先を開くとデータが古い（最悪、テーブルすら無い）——という事故が起きる。実際にどれくらい壊れるのか、Pythonの sqlite3 で検証し、安全な方法を比較した。

環境

• Windows 11（ビルド 10.0.26200）
• Python 3.11（同梱の sqlite3 ライブラリ版 3.42.0）

やったこと

1. WALモードでDBを作り、ファイル構成を見る

import sqlite3

con = sqlite3.connect("app.db")
con.execute("PRAGMA journal_mode=WAL")
con.execute("PRAGMA wal_autocheckpoint=0")   # 検証のためチェックポイントを止める
con.execute("CREATE TABLE notes(id INTEGER PRIMARY KEY, body TEXT)")
con.execute("INSERT INTO notes(body) VALUES ('既存データ')")
con.commit()
print(con.execute("SELECT COUNT(*) FROM notes").fetchone()[0])   # 1

con.execute("INSERT INTO notes(body) VALUES ('あとから追加した重要データ')")
con.commit()
print(con.execute("SELECT COUNT(*) FROM notes").fetchone()[0])   # 2

WALモードでは、コミット内容はまず app.db-wal に書き込まれ、あとで本体（app.db）へ反映（チェックポイント）される。wal_autocheckpoint=0 の間は、新しいデータの実体が本体ファイルにまだ無い状態になる。

2. 素朴なファイルコピーを試す

import shutil

shutil.copyfile("app.db", "copy_naive.db")   # 本体だけコピー

c2 = sqlite3.connect("copy_naive.db")
print(c2.execute("SELECT COUNT(*) FROM notes").fetchone()[0])

実行結果:

OperationalError: no such table: notes

テーブルすら存在しないという結果になった。スキーマ作成と2回のコミットの内容がすべてWAL側にあり、本体ファイルはほぼ空だったためだ。エラーになってくれるだけまだ良く、環境によっては「エラーにならず、古いデータが静かに返る」こともある。

3. 安全な方法1: backup API

Pythonの sqlite3 には、実行中のDBから整合性の取れたコピーを作る backup() がある。

src = sqlite3.connect("app.db")
dst = sqlite3.connect("copy_backup.db")
with dst:
    src.backup(dst)   # 稼働中のDBでも安全にコピーできる

実行結果:

backup APIの行数: 2   <- 最新の2行がコピーされている

4. 安全な方法2: VACUUM INTO

SQLite 3.27以降では、SQL1文でクリーンなコピーを作れる。

con.execute("VACUUM INTO 'copy_vacuum.db'")

VACUUM INTOの行数: 2

こちらも最新の内容が取れた。ただし仕様としてコピー先が既に存在するとエラーになる（実測: OperationalError: output file already exists）。バックアップのローテーションでは、日時入りのファイル名をつけるか、事前に削除しておく。

5. コピーの健全性チェック

print(con.execute("PRAGMA integrity_check").fetchone()[0])   # ok

ハマった点

1. 「.dbファイルをコピーすればバックアップ」はWALモードでは不成立。-wal / -shm を含めてコピーするのはさらに危険（タイミングによって不整合な組み合わせになる）。バックアップには専用APIを使う。
2. 素朴コピーの検証で、コピー先に -shm / -wal が生成される。コピーしたDBを開くとSQLiteがWAL系ファイルを新規作成するため、「pnpmで3ファイルになった」ように見える。どこまでコピーしたか分からなくなりやすい。
3. エラーにならない壊れ方もある。今回は運よく no such table と即エラーだったが、静かに古いスナップショットが返るケースもある。読んでみるまで気づけない。
4. バックアップは「取れて終わり」ではない。integrity_check と、コピー先を実際に開いて件数を確認するところまでを1セットにする。

まとめ

• WALモードのSQLiteは本体＋-wal＋-shm の3ファイル構成。動作中のファイルコピーはバックアップにならない。
• 安全な方法は Connection.backup()（Pythonならこれだけでよい）か VACUUM INTO。
• VACUUM INTO は出力先が既存だと失敗するので、日時入りファイル名＋事前削除の運用にする。
• リストア確認（開いて件数を見る・PRAGMA integrity_check）までをルーチン化する。