VIDEO–How to RECOVER Corrupted Database ?

Leave a comment (3) Go to comments

How to Shorten the recovery time in case of database corruption? OR

HOW to FIX damaged page in SQL Server ?

HOW to FIX SQL Server ERROR : 8928 ?

HOW to FIX page level corruption in SQL Server Database ?

Error 8928 Details

Msg 8928, Level 16, State 1, Line 1
Object ID 2105058535, index ID 2, partition ID 72057594038910976, alloc unit ID 72057594039828480 (type In-row data): Page (1:223) could not be processed.  See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 2105058535, index ID 2, partition ID 72057594038910976, alloc unit ID 72057594039828480 (type In-row data), page (1:223). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 2105058535, index ID 2, partition ID 72057594038910976, alloc unit ID 72057594039828480 (type In-row data). Page (1:223) was not seen in the scan although its parent (1:923) and previous (1:222) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 2105058535, index ID 2, partition ID 72057594038910976, alloc unit ID 72057594039828480 (type In-row data). Page (1:224) is missing a reference from previous page (1:223). Possible chain linkage problem.


ANSWER : I do get this question from one of the DBA that he has database of 100 GB which is live in production and when he run DBCC CHECKDB he get errors like mentioned above. He tested various other method to fix this issue and all failed. So left out with an option to perform a restore from backup only.

NOW he wanted to understand, how can he reduce the recovery time to fix this issue.

VIDEO – How to RECOVER Corrupted Database

This video will demonstrates how to perform sql server page restore ?

In case, you are unable to watch this video, here are quick Steps with Screen Shot

STEP 1 - Check Database For corruption

We can check database integrity by using DBCC CHECKDB command, to see weather there is corruption in database of not.

VIDEO–How to RECOVER Corrupted Database ?

Looking at error message, we can clearly identify that there is corruption on page 223 as we can see this message. Object ID 2105058535, index ID 2, partition ID 72057594038910976, alloc unit ID 72057594039828480 (type In-row data): Page (1:223) could not be processed. 

STEP 2 – Restore faulty page from a GOOD Backup – PAGE Level Database Restore

Now we need to restore faulty pages from a SQL Server backup, that means restore only faulty pages. This is a new feature in SQL Server 2008, where we can restore only some corrupted pages from a good database backup.

For example you have a 100 Gb database and only 1 page is corrupted than we can save recovery time by restoring a single page instead of a 100 GB database.

SQL Command to perform a page level restore

use master
go
RESTORE DATABASE DBA PAGE = '1:223' FROM DISK = 'C:\temp\DBA_before_curruption.bak';
go

STEP 3 – Backup and Restore Current TRANSACTION LOG Backup

If you read the restore informational messages, which we received in last step states that there is difference between the LSN number.

Processed 1 pages for database ‘DBA’, file ‘DBA’ on file 1.

The roll forward start point is now at log sequence number (LSN) 43000000055600001. Additional roll forward past LSN 43000000058400001 is required to complete the restore sequence.

RESTORE DATABASE … FILE=<name> successfully processed 1 pages in 0.098 seconds (0.079 MB/sec).

To correct this LSN number, we need to backup the current log and restore in a current database, using the following syntax.

use DBA
BACKUP LOG DBA TO DISK = 'C:\DBA_log.bak' WITH INIT;
GO

use master
GO
RESTORE LOG DBA FROM DISK = 'C:\DBA_log.bak';

This is going to be pretty quick as only page level transactions will be rolled back or rolled forward, you can see that in message where backup log size was in MB’s but restore was kind of ZERO only.

Processed 5 pages for database ‘DBA’, file ‘DBA_log’ on file 1.

BACKUP LOG successfully processed 5 pages in 0.020 seconds (1.684 MB/sec).

Processed 0 pages for database ‘DBA’, file ‘DBA’ on file 1.

RESTORE LOG successfully processed 0 pages in 0.006 seconds (0.000 MB/sec).

 

STEP 4 – Verify corruption has been resolved and data is consistent

Re-execute DBCC CHECKDB to ensure and verify that corruption has been removed and database is health now.

OPTION 2 – The corruption example, which I took was of Index and I want to make you understand how page level restore works, if you are looking for a solution to a exact problem, which I demonstrated, can be resolved by rebuilding a non clustered index, as can afford to rebuild index which doesn’t;t result any data loss.

EOF - VIDEO–How to RECOVER Corrupted Database ?, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

3 Comments.

  1. That was a very good artcle & explanation regarding Page Restore but I have one doubt that after restoring tha page why there was difference between the LSN number. And how the LSN will match after taking t-log backup & apply it.

    Thanks.

  2. Index ID of 2 indicates a nonclustered index is corrupted. In this scenario, I would recommend dropping and rebuilding the index instead of attempting a page level restore unles the index is really large.

    • Totally Correct, even I mentioned in that in OPTION two, The objective for this article to make user aware that how to perform a SQL server page level restore.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.