An Intuitive Guide On Page Level Restore in SQL Server

Andrew Jackson ~ Modified: January 5th, 2022 ~ SQL ~ 5 Minutes Reading

SQL Server database is used by many organizations to manage and organize their data. However, due to some catastrophic conditions the user has to face corruption in SQL Server. Sometimes, only a single SQL Server page is corrupt. In such case, restoring the complete database for just one corrupt page in the database is not a relevant option. The clever approach is to restore only that page which is corrupted.

Now, the question pops up, how can one restore the corrupt page from the most recent database backup? The user can perform the page level restore operation in SQL Server method to restore corrupt page in SQL Server. In this blog, we are going to introduce a step by step process to restore page in SQL Server.

Steps to Perform Page Level Restore in SQL Server

Now, in this section, we are first going to corrupt a page and then perform restoration process of page level to restore the page from the available backup. The user just has to follow the step by step explained below to perform the page level restore in SQL Server. The user who is having the corrupt page in SQL Server can move directly to Step 2.

Step 1: Let us Corrupt a Page

1. Here, we are going to set up a scenario, how to corrupt a page in SQL? For this, the user needs to create a new database by creating a table within it with some records. I have created Test_Page database.
2. After creating a table, perform the full database backup. This backup includes all the pages which belong to the recently created table. Full database backup is very important as in next step, we are going to corrupt one specific page of this table.
3. Now, it is the time to find out which pages belong to the table created recently, with the help of DBCC IND command. This command will return all the pages for a specific table.
4. To corrupt a page, use the undocumented command DBCC WRITEPAGE. Moreover, to use this command, the database must be set as a Single-User mode.
5. Here, we are going to simulate some storage error, where the storage just writes some garbage to a page. Now, you can get an idea from the table as the SQL Server will return an 824 I/O error. This is due to the checksum validation for the corrupted page failed.
6. SQL Server detects a corrupted page during I/O access, the corrupted page is also logged in msdb.dbo.suspect_pages.

Step 2: Restore the Corrupt Page using Page Level Restore in SQL Server

1. To restore the database into a good state, the user needs to perform the Tail-Log Backup. With the help of tail log backup, the user is backing up the transactions that have occurred since the last transaction log backup.
2. Now, initiate the operation page level restore in SQL Server. The user has to use the traditional RESTORE DATABASE T-SQL command but he/she need to specify the page that is to be restored. Instead of restoring the complete data, only a single corrupted page is to be restored.
3. After the RESTORE DATABASE command, the user has to perform transaction log backup. Now, form here tricky part begins. This additional log backup is required to confirm that all the changes performed by the user on this page are available for the restore. Without additional log backup, the SQL Server is not able to bring the page online again.
4. Once the user has performed the additional log backup, he/she can finally restore all the log backups in the correct order and bring the database online.
5. Finally, the corrupt SQL Server page is restored using the Page Level Restore process. Now, when the user will insert query in the table again, then he/she can see that the SELECT statement succeeds without any I/O errors.

Instant Way to Repair Corrupt SQL Server Database Files

Most of the users face difficulties while implementing the manual solution. Thus, to overcome all the problems faced by the users while performing the manual steps, it is suggested to opt for a smart, automated and trustworthy solution. The user can easily repair corrupt SQL Server database and export the healthy SQL Server database with the help of SQL Repair Tool.

Purchase Now
100% Secure Free Download
100% Secure

The tool allows the user to repair both primary and secondary SQL database. Moreover, it also allows the user to repair corrupt MDF and NDF files. The software is having a user-friendly interface, high performance, feature-rich nature etc. The user can also recover the deleted SQL table’s data in just a few clicks.

Final Words

Due to catastrophic conditions, corruption in SQL Server occurs. Sometimes, only a single page or few pages are corrupted instead of the full database. In such cases, the user can opt for page level restore technique to restore the page which is corrupted. Most of the times, it becomes complex for the user to implement the manual steps. Thus, the user can make use of an automated and trustworthy software that is SQL Recovery to repair the corrupt SQL Server database and it files without any data loss.