Page Restore: Fixing a Few Corrupt Pages Without a Full Restore

Restoring an entire database can be excessive when damage is confined to a few eligible pages. A page restore replaces those pages from backup and rolls them forward through the transaction log.

Diagnose the Damage Before Choosing the Repair

DBCC CHECKDB reports the affected structures and page identifiers. Preserve its output and the SQL Server error log before making changes. The numbers identify a file and page, rather than an arbitrary row. Check whether the problem is isolated or part of wider storage failure.

I involve the storage owner before restoring pages onto a suspect device. Repeated read or write errors can damage the replacement too. Review Windows events, storage paths, and recent failures. A clean replacement page does not make an unreliable device trustworthy. The floor matters as much as the tile.

This demonstration belongs entirely to a test database named PageRestoreLab. It assumes prepared backups and verified page identifiers. It does not intentionally corrupt files. Do not copy sample page numbers into production. Restore operations change availability and require an established recovery procedure.

Read the Page Inventory and Integrity Report

The suspect_pages table records detected page problems and their status. Read it together with CHECKDB output. Entries can reflect earlier damage that was already restored or repaired. An empty inventory is also incomplete evidence when the damaged page has not been encountered or the inventory reached its limit.

SELECT database_id, file_id, page_id, event_type,
       error_count, last_update_date
FROM msdb.dbo.suspect_pages
WHERE database_id = DB_ID(N'PageRestoreLab')
ORDER BY last_update_date DESC;
DBCC CHECKDB (N'PageRestoreLab')
WITH NO_INFOMSGS, ALL_ERRORMSGS;

Event types one through three describe active error categories. Four and five indicate restored or repaired pages. Use the current integrity report to decide which entries still matter. Preserve the original evidence rather than clearing the table merely to make the report look clean.

Confirm That the Pages Qualify for a Page Restore

The operation uses data pages in read/write filegroups. It cannot bring back transaction-log pages, allocation pages such as GAM, SGAM, and PFS, file header page zero, or database boot page 1:9. Damage to critical metadata can require a different restore strategy. Identify page purpose before selecting this method.

A usable full recovery backup chain is the straightforward prerequisite. Bulk-logged recovery introduces restrictions that make this path unsuitable in important cases. A simple-recovery database lacks the required log chain. Changing the recovery model after damage does not manufacture missing historical log backups.

Enterprise supports online page restoration for suitable scenarios. Other editions use an offline path. Even an Enterprise database needs offline recovery when critical damage prevents online work. Plan the availability impact explicitly. The sample below follows the documented online sequence on an eligible Enterprise test installation.

Establish the Backup Chain in the Lab

Start with an existing healthy lab database under FULL recovery. Use a new backup folder accessible to the SQL Server service account. These commands illustrate establishing the full backup and later log backups before a restore rehearsal. Use fresh filenames and retain their order.

ALTER DATABASE [PageRestoreLab] SET RECOVERY FULL;
BACKUP DATABASE [PageRestoreLab]
TO DISK = N'C:\SqlBackups\PageLabFull.bak'
WITH CHECKSUM;
BACKUP LOG [PageRestoreLab]
TO DISK = N'C:\SqlBackups\PageLabLog1.trn'
WITH CHECKSUM;
BACKUP LOG [PageRestoreLab]
TO DISK = N'C:\SqlBackups\PageLabLog2.trn'
WITH CHECKSUM;

In a prepared rehearsal, application changes occur between the chosen backups. The example does not claim that any particular change was observed. Inspect backup headers and validate that the chain covers the selected recovery point. A folder sorted alphabetically cannot certify log sequence or database identity.

Run the Page Restore on the Selected Pages

Choose the full backup containing the identified pages. An applicable differential backup can reduce the subsequent roll-forward work. Then restore every required log backup in chain order. Leave the sequence unrecovered until the final required log is available. Premature recovery closes the sequence too soon. Run these commands from master, because RESTORE refuses a database that your own session is using.

USE master;
RESTORE DATABASE [PageRestoreLab]
PAGE = '1:100,1:101'
FROM DISK = N'C:\SqlBackups\PageLabFull.bak'
WITH NORECOVERY;
RESTORE LOG [PageRestoreLab]
FROM DISK = N'C:\SqlBackups\PageLabLog1.trn'
WITH NORECOVERY;
RESTORE LOG [PageRestoreLab]
FROM DISK = N'C:\SqlBackups\PageLabLog2.trn'
WITH NORECOVERY;

The identifiers are placeholders for verified eligible pages in your lab. Replace them before execution. SQL Server needs the selected pages to reach a state consistent with the database. Restoring their old contents without replaying the changes would produce a database with conflicting points in time.

Capture the Final Log at the Right Stage

The online sequence needs a new log backup after page restoration starts. It must include the required final log position for the pages being recovered. The engine cannot use the current online log directly as that restore input. Capture the final log backup and restore it to finish the sequence.

BACKUP LOG [PageRestoreLab]
TO DISK = N'C:\SqlBackups\PageLabFinal.trn'
WITH CHECKSUM;
RESTORE LOG [PageRestoreLab]
FROM DISK = N'C:\SqlBackups\PageLabFinal.trn'
WITH RECOVERY;

For an offline procedure, capture the tail log before taking the database through its restore sequence. BACKUP LOG WITH NORECOVERY can put it into restoring state. The required order differs from the online example. Preserve the tail and follow the procedure for that scenario instead of combining commands from both paths.

I rehearse the entire chain rather than testing only the PAGE command. A missing middle log or inaccessible final backup defeats the recovery plan. Validate encryption keys and backup locations too. A recovery procedure needs every dependency available where the restore will actually run.

Verify the Page Restore and the Underlying Storage

Run CHECKDB again and test representative application reads. Compare affected business records with accepted evidence from the rehearsal. Retain the completed sequence and the status changes. Keep the original storage findings too. Review whether the device was repaired or replaced and whether the same error pattern has recurred. The database check and the storage review answer different parts of the recovery question. A successful command is one checkpoint, while consistent data and healthy storage complete the investigation.

DBCC CHECKDB (N'PageRestoreLab')
WITH NO_INFOMSGS, ALL_ERRORMSGS;
SELECT file_id, page_id, event_type, last_update_date
FROM msdb.dbo.suspect_pages
WHERE database_id = DB_ID(N'PageRestoreLab');

Which symptom tells you the damage extends beyond the selected pages? New page errors, widespread integrity findings, or continued device failures call for a broader recovery decision. Avoid repeating a narrow repair simply because it restored availability once. Escalate the scope when the evidence changes.

A page restore is valuable for isolated eligible damage with a complete backup chain. Keep storage diagnosis, recovery prerequisites, and verification together. A page restore earns its place through a tested sequence, rather than a promise that any corrupt database can be fixed one page at a time.

Related reading on this blog: Piecemeal Restore: Bringing the Primary Filegroup Online First and Page Verify CHECKSUM and Why It Matters.

A page restore is not a cure for failing storage, it is targeted recovery from a valid backup chain.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.