Page-level restore in SQL Server

In Microsoft SQL Server, the "page-level restore" feature lets you fix corruption or damage in individual data pages without restoring the whole database. This is useful when only a small part of a large database is affected.

To use a page-level restore, you will need at least:

• A full backup, plus any differential and log backups.
• Page IDs you want to restore (from DBCC CHECKDB or error messages).
• Database using FULL or BULK_LOGGED recovery.

Example: to restore a single page from the latest full backup and then roll forward log backups:

-- Put database in full or restricted access first
ALTER DATABASE MyDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- Restore the damaged page from full backup
RESTORE DATABASE MyDb
  PAGE = '1:12345'         -- file:page
  FROM DISK = 'D:\Backups\MyDb_full.bak'
  WITH NORECOVERY;

-- Apply log backups
RESTORE LOG MyDb
  FROM DISK = 'D:\Backups\MyDb_log1.trn'
  WITH NORECOVERY;

RESTORE LOG MyDb
  FROM DISK = 'D:\Backups\MyDb_log2.trn'
  WITH RECOVERY;

ALTER DATABASE MyDb SET MULTI_USER;

Always test this procedure on a non-production restore first, and use DBCC CHECKDB afterwards to verify that the database is healthy.