maandag 9 september 2013

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