In this article, we look at the concept of restoring pages in SQL Server instead of performing a file recovery.
Many times, while using SQL Server databases, you might come across one of the pages in the application that is corrupt. When a page out of the complete database is corrupt, there is no need of restoring the whole database, you can simply restore the corrupted page. For restoring corrupted pages in SQL Server, you can either make use of the SQL Server Management Studio (SSMS), or T – SQL. Except for the Enterprise Edition, you will be required to perform an offline restore before performing an online restore. In the Enterprise edition, the offline restore is not mandatory.
When should you Restore pages
Restoring pages is quicker than restoring a file, however you can restore pages when there are only few damaged pages; for a greater number of pages, you will have to opt for a file restore. If multiple pages are damaged, it might mean a device failure, in such a situation, you can restore on a separate location and put the system on repair. Restoring pages is not recommended for all kinds of errors.
Ideal Scenarios for Restoring Pages in SQL Server
- Offline – Offline page restore is supported by SQL editions. It means exactly what it sounds like – restoring pages offline involves restoring when database is offline. When restoring pages offline, the database becomes inaccessible at the time of restore, and becomes accessible after the restore is done.
- Online – Restoring pages online involves restoring when database is online, the Enterprise edition supports this form of restoration. Along with the filegroup, where the page is being restored, many editions also require the database, containing the corrupted page, to also be online. It does not matter whether the secondary groups are online or not, the primary filegroup should be online for the page to be restore successfully.
*Sometimes it might be possible to only opt for Offline page restore, if the database does not open due to excess damage of pages.
Drawbacks of Restoring Pages
- Only pages belonging to databases using the Full or Bulk – Logged Recovery Model can be restored.
- The feature is supported apparently for read/write filegroups only
- Page Restore can only be used for database pages, and not for pages from the transaction log, Full text catalog, File boot page, Database boot page, etc.
There can be multiple reasons as to why a page in a database would get corrupted. The corruption of one or a more pages can be repaired by the Page Restore feature in SQL Server. Page restoration can either be done using SSMS or T- SQL, it can either be done when the database is online or offline. Page restore is very beneficial when you do not have to recover the whole database, but just a few pages. It takes little time and provides a reliable solution, however it should not be used when corruption spreads to multiple pages; instead a proper mdf recovery tool should be called in.
Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit www.datanumen.com