Home > Backup and Restore > Rectified the Page corruption issue by page restore option

Rectified the Page corruption issue by page restore option


Rectified the Page corruption issue by page restore option

The database corruption is very common issue in DBA world but we can restore the specific page which is corrupted by page restore option. We have implemented successfully in our project and written the below article If the DBCC CHECKDB  job has failed and as an output  we have received the below output.

Object ID 2088535921, index ID 0, partition ID 72345201021503994, alloc unit ID 72345201051571606 (type In-row data): Page (1:94299) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 2088535921, index ID 0, partition ID 72345201021503994, alloc unit ID 72345201051571606 (type In-row data), page (1:94299). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed.
CHECKDB found 0 allocation errors and 2 consistency errors in table ‘ DimSalesReason’ (object ID 2088535921).
CHECKDB found 0 allocation errors and 2 consistency errors in database ‘ AdventureWorksLT2008’.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (AdventureWorksLT2008).

 

Work around:

From this you can see what page is corrupted (1:94299)

The first thing to do is check if it is data in a heap, in a clustered index, or in a non-clustered index. In the above text you can see it is index ID 0. You could also examine the page (1:94299 in database ‘yourdb’) as follows:

DBCC TRACEON (3604, -1)
GO
DBCC PAGE(‘ AdventureWorksLT2008’, 1, 94299, 3)
GO
In the output you will see something like:

Metadata: IndexId = n

Note: If n is greater than 1 it is a non-clustered index and can safely be dropped and recreated. If n is 0 or 1 you have data corruption and need to perform one of the options described below.
Option  1: Restoring specific corrupted  page  from a backup
If the recovery model is FULL (or BULK_LOGGED, with some limitations), you can back up the tail of the log, perform a restore (with no recovery) from the last clean full backup, followed by subsequent log backups and finally the tail of the log.

If only a few pages are affected you have the option of selectively restoring only the bad pages, as follows:

RESTORE DATABASE AdventureWorksLT2008 PAGE = ‘1:94299’
FROM DISK = ‘E:\ AdventureWorksLT2008.bak’
WITH NORECOVERY
Option 2: If the recovery model is simple you don’t have that option, and have to accept that a restore from the last full backup will result in subsequent transactions being lost. In this case, or if you have no backups at all, you may decide that an automatic repair is the only option.

 

ALTER DATABASE AdventureWorksLT2008 SET EMERGENCY;
GO

ALTER DATABASE AdventureWorksLT2008 SET SINGLE_USER;
GO
DBCC CHECKDB (AdventureWorksLT2008, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO

ALTER DATABASE AdventureWorksLT2008 SET MULTI_USER;

Advertisements
Categories: Backup and Restore
  1. February 12, 2013 at 2:43 pm

    Good day! This is my first visit to your blog! We are a team of volunteers and starting a new project in a community in the same niche. Your blog provided us useful information to work on. You have done a extraordinary job!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: