Database repair Part2: Page restore

We have created a new upt database. Today we will use the page to restore the damaged page.

 

First, we allow DBCC checkdb to view the damaged page ID:

 

DBCC checkdb withno_infomsgs

 

MSG 8928, level 16, state 1, line 1

Object id2105058535, index ID 0, partition ID 72057594038779904, alloc unit id72057594039828480 (type in-row data): page (1: 78) cocould not be processed. See other errors for details.

MSG 8939, level 16, State 98, line 1

Table error: objectid 2105058535, index ID 0, partition ID 72057594038779904, alloc unit id720575940398281_( type in-row data), page (1: 78 ). test (is_off (buf_ioerr, pbuf-> bstat) failed. the values are 12716041 and-4.

Repair_allow_data_loss is the minimumrepair level for the errors found by DBCC checkdb (writable upt ).

 

The recommended solution is repair_allow_data_loss. However, if you use repair_allow_data_loss to fix the vulnerability, data loss may occur and Data Consistency may occur. After SQL Server 2005, page restore is provided. With page restore, we can directly repair this damaged page.

 

Command: --- restore upt. Bak is a backup before the database is damaged

 

Restore database snapshot upt page = '1: 78 'fromdisk
= 'D: \ MSSQL \ upload upt. Bak 'withnorecovery

 

Backup log corrupttodisk = 'd: \ MSSQL \ snapshot upt. trn'

 

Restore log corruptfromdisk = 'd: \ MSSQL \ snapshot upt. trn' withrecovery

 

Page restoration is used to repair isolated damaged pages. Restoring and recovering a small number of pages may be faster than restoring a file, reducing the amount of data offline in The Restoration Operation.

 

Restore page steps:

1. Get the page ID of the damaged page to be restored.

2. Restore the page from the full database backup, file backup, or file group backup on the page. In the Restore database statement, use the page clause to list the page IDs of all pages to be restored.

3. Recent differences in applications.

4. Apply subsequent log backup.

5. Create a new database log backup to include the final lsn of the restored page, that is, the offline time of the last restored page. The final lsn restored first in order is the target lsn redone. Online rollback of files containing this page can be stopped at the redo target lsn.

6. Restore the new log backup. After the new log backup is applied, the page is restored and the page can be used.

 

For more page restore information, see: http://msdn.microsoft.com/zh-cn/library/ms175168.aspx