SQLServer Page restore (SQLServer 2005 and later)

Page Restore (SQLServer 2005 and later):

A page restore is intended for repairing isolated damaged pages. Restoring and recovering a few individual pages might be faster than a file restore, reducing the amount of data that is offline during a restore operation. However, if you have to restore more than a few pages in a file, it is generally more efficient to restore the whole file.

Limitations and Restrictions

• Page restore applies to SQL Server databases that are using the full or bulk-logged recovery models. Page restore is supported only for read/write filegroups.
• Only database pages can be restored. Page restore cannot be used to restore the following:
• Transaction log
• Allocation pages: Global Allocation Map (GAM) pages, Shared Global Allocation Map (SGAM) pages, and Page Free Space (PFS) pages.
• Page 0 of all data files (the file boot page)
• Page 1:9 (the database boot page)
• Full-text catalog
• Online: Enterprise Edition
• Offline: all other editions

Performing a page Restore (scenario)

1) Try to find the damaged paged, if you have a lot of damaged pages, perform a full restore

• DBCC CHECKDB

 

2) Restore page(s) (from Full, File or Filegroup Backup)

•  USE master
    GO
    RESTORE DATABASE MyDb PAGE = '1:345'
       FROM DISK = N'E:\Backup\MyDb.bak'
    GO

Note: you cannot restore additional tlogs afterwards


4) Perform a taillog Backup

• BACKUP LOG MyDb
     TO DISK = N'E:\Backup\MyDb_log.TRN' 
     WITH INIT, NO_TRUNCATE, 
     STATS = 10 
  GO  

4) Restore the Taillog Backup

• RESTORE LOG MyDb 
     FROM DISK = N'E:\Backup\MyDb_log.TRN'
     WITH RECOVERY,  
     STATS = 10  
  GO