Restoring a Single Data Page in SQL Server: A DBAs Guide

Introduction

Why page‑level restore belongs in every DBA’s toolbox

Most of the time, corruption in SQL Server is either nonexistent or so widespread that you have no choice but to perform a file or full‑database restore. Yet an awkward middle ground exists: a handful of pages—perhaps only one—become unreadable while the rest of the database remains perfectly healthy. A full restore would repair the damage, but at the cost of rolling back hours of work and locking users out of an otherwise functional system.

That is precisely why Microsoft built RESTORE … PAGE. When you meet a short list of prerequisites (FULL or BULK_LOGGED recovery model, an unbroken backup chain, and a page that is not allocation metadata), you can surgically overwrite just the bad 8‑KB chunks, roll them forward with transaction‑log backups, and return the database to service in minutes rather than hours.

A practical scenario

Imagine the last integrity check finished cleanly at 01:00. At 09:13, the monitoring system flags error 824—“logical consistency‑based I/O error”—coming from page 1:11337 of SalesDB. Every application call that touches that row now fails, but other queries run fine.

SELECT file_id, page_id, event_type, error_count
FROM msdb.dbo.suspect_pages
WHERE database_id = DB_ID('SalesDB');

The query confirms two rows in suspect_pages with event_type = 1 (I/O corruption detected). Because the database uses the FULL recovery model and you take log backups every fifteen minutes, you have everything needed for a page restore.

Double‑checking your safety net

1. Verify the full backup that will provide the clean page image:

   RESTORE VERIFYONLY
     FROM DISK = 'X:\Backups\SalesDB_Full_0100.bak';

2. Verify the differential (if any) and all subsequent log backups.
   If any file fails VERIFYONLY, stop—page restore cannot proceed.
3. Take a tail‑log backup to freeze the log chain and capture the last few minutes of work:

   BACKUP LOG SalesDB
     TO DISK = 'X:\Backups\SalesDB_Tail_0915.trn'
     WITH NO_TRUNCATE;

Executing the restore—step by step

1. Restore the page image from the full backup.

   RESTORE DATABASE SalesDB
       PAGE = '1:11337, 1:11338'
       FROM DISK = 'X:\Backups\SalesDB_Full_0100.bak'
       WITH NORECOVERY;

2. Apply the differential (if present).

   RESTORE DATABASE SalesDB
       PAGE = '1:11337, 1:11338'
       FROM DISK = 'X:\Backups\SalesDB_Diff_0500.bak'
       WITH NORECOVERY;

3. Roll forward every transaction‑log backup in order.

   RESTORE LOG SalesDB
       FROM DISK = 'X:\Backups\SalesDB_Log1.trn'
       WITH NORECOVERY;
   
   RESTORE LOG SalesDB
       FROM DISK = 'X:\Backups\SalesDB_Log2.trn'
       WITH NORECOVERY;
   -- repeat for each log file

4. Restore the tail‑log backup WITH RECOVERY.

   RESTORE LOG SalesDB
       FROM DISK = 'X:\Backups\SalesDB_Tail_0915.trn'
       WITH RECOVERY;

What SQL Server does behind the curtain

During each step SQL Server:

• Validates the page checksum before writing it to the data file.
• Ensures the page’s pageLSN is not newer than the first log record to be applied.
• Replays only the log records that reference those pages, advancing pageLSN to the database‑wide recovery point.
• Updates msdb.dbo.suspect_pages from event type 1 (corrupt) to 4 (restored) after WITH RECOVERY.

On Enterprise Edition, locks are scoped to the individual pages; Standard Edition keeps the database offline until recovery completes. In either edition the entire operation is fully logged, so crash recovery can finish the work if the server fails mid‑restore.

Post‑restore validation

Even after a textbook restore, always run a quick integrity check:

DBCC CHECKDB ('SalesDB') WITH PHYSICAL_ONLY;

If that passes, schedule a full DBCC CHECKDB in your next maintenance window and take a fresh full backup to establish a clean baseline.

Situations where page restore is not the right tool

• Corruption involves allocation pages (GAM, SGAM, PFS) or system catalogs.
• Hundreds of pages are corrupt—it’s faster to restore the entire file.
• The database is in SIMPLE recovery and lacks a usable log chain.
• Any backup in the required chain is missing or fails verification.

Operational tips from the field

• Automate detection. Poll suspect_pages and alert when new rows appear.
• Rehearse. Corrupt a page in a sandbox and practice the full sequence.
• Keep backup retention practical. Page restore needs the oldest backup that contains a clean page image.
• Monitor progress. Query sys.dm_exec_requests for percent_complete on the restore session.

Conclusion

Page‑level restore seldom makes headlines, yet when a single bad sector flips a bit it can save hours of downtime. Instead of rolling back half a day’s work, you overwrite sixteen kilobytes, replay a handful of logs, and move on. By rehearsing the procedure and keeping backups healthy, you turn single‑page corruption from a crisis into a routine fix—one more reason disciplined DBAs sleep better at night.