Page Level Restore In SQL Server 2012

How to do Page level restore in SQL Server 2012.

We found that our DBCC checkDB job got failed due to some inconsistency error and we dig more into this error and finally found that there is one page has been corrupted.

The Restore Pages feature let us restore damaged pages without restoring the entire database. These pages are marked as suspect.

Restore pages is only possible in the full and bulk‑logged recovery model. We can only restore database pages.

How we come to know page is really corrupted:-

Step 1:

 we checked and verified error log in SQL Server and found below error:-

Step 2:-

Run below command and found corrupted page information as per above error for database id 20 

Step 3:-

We found the database name with database id 20 with below command.

Step 4 :-

As of now we have found database name now we need to find that object from where page has corrupted.

For below command you can achieve this.

Step:5

Once you got the object ID you can find the table name by below command

Step :6

Now we try to run more command DBCC CHECKTable but we got similar error:-

Step :7

Finally we decide to look into corrupted page id with below command:-

Step 8:

Finally we decided to Page restore by SSMS 2012 but for Page restore make sure your database on full recovery mode.

In order to restore the page, I select the database, I right‑click on it (from the Object Explorer in

the SSMS) then select Tasks‑>Restore‑>Page .

You can see below corrupted Page id and file id automatically

  

 Click on the ok Button

STEP :9

After restoration we ran again check DB and it was success

Same as DBCC Checktable