Page Restore On SQL Server

 

When the database falls into the suspect mode, we can solve this problem by restoring only the corrupted pages instead of  restoring full backup. You can find suspect pages with the help of the following script.

The suspect_pages table may change after the following situations.

  • When a query want to read a data
  • After the DBCC CHECKDB command is executed
  • After backup is taken

 

Let’s confirm the pages we will restore using the following script.

After this process, let’s run the following script again.

Let’s try to get a tail log backup with the help of the following script from the database that was falled to the suspect mode before starting the Restore process. Tail Log Backup takes the backup of the log records after the last log backup. You should not use NO_TRUNCATE in a healthy database.

After finding the suspect pages, we write file_ids and Page IDs to the relevant sections in the script below.

1 file id
58 and 200 page ids
Finally, we restore the tail log backup in recovery mode and complete the process.
Page Restore can also be performed with SQL Server 2012 via SSMS. Right-click on the appropriate database, and then click Task-restore-page.

We’ll enter the page or pages that we’ll restore by clicking Add in the Pages screen. Of course, the backups that appear in the “backup sets” section on the bottom side must be in those paths. You can confirm the accuracy of those files before performing the restore operation by clicking Verify.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

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