Have you ever tried to access the page that marked as 'suspected'. You can easily identified suspected pages in the suspect_pages table in the msdb database. If yes, then you must be aware that user cannot access these pages. To access these pages, first you have to restore the pages.
In this case, we should use page restore option. The option helps users to restore one or more damaged pages without restoring the whole database.
Page restore divided in to two parts:
Offline Page Restore option restores damaged pages when database is in offline mode. When the restore operation gets completed then the database automatically comes online.
Online Page Restore option restores damaged pages when database is in online mode. In some cases the condition comes that primary filegroup is online & ne or more of its secondary filegroups are offline, in this case page restores are usually performed online.
To restore pages through RESTORE DATABASE statement, you need page ID of the page & the file ID of the file containing the page. Here is the syntax:
RESTORE DATABASE <database_name>
PAGE = '<file: page> [ ,... n ] ' [ ,... n ]
FROM <backup_device> [ ,... n ]
WITH NORECOVERY
Points to be considered:
- Page restore only supported for full or bulk-logged recovery models.Page restore is supported only for read/write filegroups.
- You can only restore database pages.
- For the databases that are using the bulk-logged recovery model, page restore has some conditions.
In what cases we should not prefer this option:
- If you want to restore more than some of pages in a file, it is more recommended to restore the whole file.
- All page errors are not fixed by page restore.
Hope the article will help you to restore SQL Server suspected pages.
No comments:
Post a Comment