Windows.db Forensics: SQLite, WAL and Gather Database

TL;DR. On Windows 11 the Windows Search index is SQLite in write-ahead-log mode. Windows.db holds the property store (SystemIndex_1_PropertyStore + _Metadata). Windows.db-wal holds committed transactions not yet checkpointed into it. Windows-gather.db (+ its own WAL) holds the gatherer's SystemIndex_Gthr and SystemIndex_GthrPth tables. Collect all of them together. Read the database with the WAL for the current state, and compare with the database without it to see what the last transactions added, changed or removed. Never open the originals in a normal SQLite client: it can checkpoint and wipe that history.

The files

The table and column names come from public research: Stroz Friedberg's 2023 paper and Kaspersky's Windows 11 artefacts article. Microsoft does not document the schema. Byte 18 and 19 of the SQLite header are both 2 when a database is in WAL mode, which is a quick way to confirm you need the -wal file.

How the WAL works (the parts that matter)

From the SQLite WAL documentation and the file format specification:

• A write does not modify Windows.db. SQLite appends the new versions of the changed pages to Windows.db-wal, as frames.
• A transaction is committed when its last frame is written with a commit marker (the database size after commit).
• A reader looking for a page takes the latest committed frame for that page in the WAL; if there is none, it reads the page from the database.
• A checkpoint copies the latest version of each page back into the database. By default it happens when the WAL reaches 1,000 pages, or when the last connection closes.
• After a checkpoint the WAL is normally not truncated. SQLite starts writing again from the beginning and changes the header salts. Frames from the previous generation beyond the new write position remain on disk until overwritten.

Each frame has a 24-byte header with the page number, the commit marker, the two salts and a running checksum. That is how a reader knows which frames are valid, committed and current.

What that gives the investigator

1. The current state

Database plus WAL (committed frames only) is what the Windows Search service itself would read. That is the view to report on. Without the WAL you may be looking at an index that is minutes, hours or days old. In practice this can mean exactly the files created during the incident are missing.

2. What changed recently

Compare the database alone with the database plus WAL:

The Windows Search Index Parser does this comparison for you and marks items only in WAL and changed in WAL. It does not list the third case separately yet; comparing exports with and without the WAL shows it.

3. Older versions of pages

A single WAL can contain several frames for the same page, from successive transactions. Plus, after a checkpoint and restart, stale frames from the previous generation (with old salts) can remain at the end of the file. Both are potential sources of older versions of records, including records deleted since. Recovering them requires a WAL-aware carver. The parser applies only valid, committed frames of the current generation and ignores the rest, reporting why (salt change, checksum mismatch, uncommitted frames) in its warnings.

The gather database is not optional

Without Windows-gather.db:

• You lose folder paths from the gatherer's scope tree. SystemIndex_GthrPth stores each folder as (Scope, Parent, Name); walking up the parents rebuilds the path, and SystemIndex_Gthr.FileName completes it.
• You lose the cross-check between the property store and the gather table (DocumentID = WorkId). An item present in the store but absent from the gather table is one of the best "deleted?" indicators; see evidence of deleted files.
• You lose the gatherer's own LastModified value, a second opinion on the file's modification time.

The gather database has its own WAL. Collect it too. The parser pairs Windows-gather.db and its -wal with the Windows.db of the same folder, so keep the folder structure when you collect from several machines.

Mistakes that destroy evidence

• Opening the original in a SQLite GUI. Reading may be harmless, but closing the last connection triggers a checkpoint by default: the WAL is merged into the database and can be deleted. Work on a hashed copy.
• Copying Windows.db and the WAL at different moments. You get frames that do not match the database pages. A careful reader validates salts and checksums and drops the WAL. The parser also checks that the resulting property store is plausible and ignores a WAL from another database with a warning.
• Stopping the service to "unlock" the files. A clean stop closes the connections, which checkpoints. You end up with a tidy Windows.db and no before/after comparison. See acquisition options.
• Dropping the -shm file into your tooling as if it mattered. It is a cache. The parser lists it as not needed.

Verifying a parser with sqlite3

On a copy of the folder, the SQLite shell gives you ground truth for spot checks:

-- property names
SELECT Id, Name FROM SystemIndex_1_PropertyStore_Metadata WHERE Name LIKE 'System.ItemPath%';

-- every property of one item
SELECT m.Name, hex(p.Value), p.Value
FROM SystemIndex_1_PropertyStore p
JOIN SystemIndex_1_PropertyStore_Metadata m ON m.Id = p.ColumnId
WHERE p.WorkId = 532;

Because the shell applies the WAL, it shows the current state; copy the database without its WAL to a separate folder to query the checkpointed state. The shell may checkpoint on exit, which is fine on a throwaway copy and one more reason never to do it on the original.

Honest limits of the tool here

The Windows 11 support in the Windows Search Index Parser is built from public research and tested on SQLite databases shaped like the real ones, including a WAL left uncheckpointed. The exact DDL, the storage-type codes beyond text (11) and integer / FILETIME (12), the path reconstruction and the byte order of the gather LastModified are heuristics that still need validation on more real-world Windows.db files. Recovering records from freelist pages, freeblocks or stale WAL frames is on the roadmap, not in the tool.

FAQ

Can I open Windows.db with DB Browser for SQLite or the sqlite3 shell?

Yes, on a copy. A normal SQLite client applies the WAL when it reads, and may checkpoint it into the database and reset it when it closes. That destroys the before/after comparison, so never do it on the original evidence.

Why does my Windows.db look almost empty?

Either the WAL was not collected and most recent work is still in it, or the tool you used does not read WITHOUT ROWID tables or does not pivot the property rows. Check the size of Windows.db-wal and try another reader.

Related articles

• Windows.edb vs Windows.db
• Where Windows.edb and Windows.db are stored, and how to collect them
• How to open Windows.edb and Windows.db