Backup & Recovery from First Principles, Part 7: Page-Level Restore – Fixing 8 KB Instead of 8 TB
Storage corrupted one 8 KB page in a large table. One option: restore the entire database from backup, losing hours to I/O. The better option, when the damage is small and isolated: restore just that page from the same backups, while the rest of the database stays online.[1]
This is part 7 of the backup and recovery series (part 1, part 6). Demo ran on SQL Server 2019 (CU32) and behaves identically on SQL Server 2025 (RTM). Page restore requires FULL or BULK_LOGGED recovery; online page restore is Enterprise Edition (Standard does it offline).
Manufacturing Corruption
To demo the repair we need a patient. With a clean full backup in hand (page_full.bak), I corrupted one page of a 10,000-row table using DBCC WRITEPAGE – the same technique from how to corrupt a database. Never run DBCC WRITEPAGE on anything you care about; it exists for building test scenarios like this one. First, find a page owned by the table with sys.dm_db_database_page_allocations, then flip a byte:
|
1 2 3 4 5 6 7 8 |
/* identify one data page of dbo.victim, then: */ ALTER DATABASE [bkrec_demo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DBCC WRITEPAGE ([bkrec_demo], 1, 113472, 2000, 1, 0xFF, 1); /* directOrBufferPool=1 bypasses checksum fix-up */ ALTER DATABASE [bkrec_demo] SET MULTI_USER; SELECT COUNT(*) FROM [dbo].[victim]; |
|
1 2 3 4 5 |
Msg 824, Level 24, State 2 SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xcf0de64b; actual: 0xcf8d664b). It occurred during a read of page (1:113472) in database ID 8 ... Complete a full database consistency check (DBCC CHECKDB). |
Two things happen on that failed read. The query dies with severity 24 (which also kills the connection – if you are scripting a demo like this, split it across batches). And SQL Server records the page in msdb.dbo.suspect_pages:[2]
|
1 2 |
SELECT [database_id], [file_id], [page_id], [event_type], [error_count], [last_update_date] FROM [msdb].[dbo].[suspect_pages]; |
|
1 2 3 |
database_id file_id page_id event_type error_count ----------- ------- ------- ---------- ----------- 8 1 113472 2 1 |
event_type 2 means a bad checksum. Monitor suspect_pages; it is the earliest warning of storage problems you will get, and it is exactly the input RESTORE PAGE needs. If you want to inspect the damage first, DBCC PAGE will show you the mangled bytes.
The Page Restore Sequence
The shape mirrors a normal restore (part 5): restore the page from the full backup NORECOVERY, then roll the page forward through the log chain to match the rest of the database, which never went offline:
|
1 2 3 4 5 6 7 8 9 10 11 |
RESTORE DATABASE [bkrec_demo] PAGE = '1:113472' FROM DISK = N'C:\temp\bkrec\page_full.bak' WITH NORECOVERY; /* apply any log backups taken since that full, then cap the chain: */ BACKUP LOG [bkrec_demo] TO DISK = N'C:\temp\bkrec\page_tail.trn' WITH INIT, COMPRESSION; RESTORE LOG [bkrec_demo] FROM DISK = N'C:\temp\bkrec\page_tail.trn' WITH RECOVERY; SELECT COUNT(*) AS [row_count] FROM [dbo].[victim]; |
|
1 2 3 4 5 6 7 8 |
row_count --------- 10000 /* suspect_pages now: */ event_type ---------- 4 |
All 10,000 rows readable again, and suspect_pages flips the entry to event_type 4 (restored). The final log backup + restore pair looks odd the first time you see it, but it is essential: the freshly restored page is older than the rest of the database, and the log replay is what catches it up. The database stays online throughout (Enterprise); only reads touching the damaged page fail while the repair runs.
The Rule That Bites: ALL the Log Backups
While building this demo I hit the failure mode worth more than the happy path. My log chain had a gap (earlier demos had taken log backups to files I had since overwritten), and the roll-forward refused:
|
1 2 |
Msg 4305, Level 16, State 1 The log in this backup set begins at LSN ... which is too recent to apply ... |
Page restore requires an unbroken log chain from the full backup you restored the page from, all the way to the present. Not to some point in time of your choosing – a page restore always rolls forward to now, so every single log backup since that full must exist and be applied in order. If any link is missing (part 3 catalogued the ways), page restore is off the table and you are doing a full database restore after all – which is how my demo database was ultimately healed, via RESTORE ... WITH REPLACE from the clean full.
Practical consequences:
- Page restore gets harder the older your last full backup is. A week of 15-minute log backups is 672 files to apply. Frequent fulls or differentials keep the roll-forward short (differentials work in page restore sequences too).
- It fixes data pages only. Damage to allocation pages (GAM, SGAM, PFS), boot pages, or file headers requires a full restore.
DBCC CHECKDBoutput tells you which kind you have. - Fix the cause, then the symptom. A page restore repairs the page; it does not repair the storage that corrupted it. Corruption is a symptom – involve the storage/infrastructure team before it recurs at scale.
Which raises a question: my clean full backup – was it actually clean? Would I have noticed if it were not? Part 8 is about exactly that: CHECKSUM, VERIFYONLY, and why “the backup job succeeded” proves less than you think.
Have you ever repaired a database 8 KB at a time? Comments below, or find me on Bluesky or LinkedIn.
References
- Restore Pages (SQL Server) – Microsoft Learn. Requirements, limitations, and the roll-forward sequence for page restore. ↩
- Manage the suspect_pages Table (SQL Server) – Microsoft Learn. event_type values and maintenance of the suspect_pages table. ↩