Tuesday, October 16, 2007

Restore a damaged page

Individual pages can be restored from a full data backup or file backup (or filegroup). Transaction log and differential backups cannot be used as a source for page level restore. you must restore transaction log backups, however, after you restore the damaged page to make the database consistent.
Generally, damaged pages will be listed in the error log of a database. To obtain ID of damaged pages, run the statement:
SELECT * FROM msdb..suspect_pages


Backup the active part of the transaction log(assuming you create the dataDISK):
BACKUP LOG AdventureWorks to dataDISK='active_log.bak',NO_TRUNCATE

Restore damaged pages from the most recent full backup:
RESTORE DATABASE AdventureWorks PAGE='3:3456'
FROM dataDISK WITH FILE='1',NORECOVERY

Restore transaction logs: NORECOVERY
Restore the active transaction log: RECOVERY