Webamster, website owners, and online providers often want to perform page level restore in sql server.

Generally, during a single database page damage users want to restore it without affecting the entire database.

As a part of our Server Management Services, we help our Customers to backup and restore website files and databases regularly.

Let us today discuss on the steps for page level restore.

Why do we need to perform page level restore in sql server?

Users sometimes face situations where a particular portion of the database get corrupted. Often database restoration is the only possibe method to bring the website/service online.

Restoring the entire database will overwrite the contents that were not affected as well. Thus most users prefer for a page level restore.

A page restore intend to repair isolated page damage. Restore of a few individual pages might be faster than a file restore. It also reduce the amount of data that is offline during a restore operation.

However, if we have to restore more than a few pages in a file, it is generally more efficient to restore the whole file.

Let us now discuss the steps for page level restore.

Page level restore using SQL Server Management Studio

SQL Server Management Studio supports page restores from SQL Server 2019 (15.x). We could perform this restore with the sequence of steps given below:

1. First connect to the appropriate instance of the SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
2. Now, expand Databases. Depending on the database, either select a user database or expand System Databases, and then select a system database.
3. Right-click the database, point to Tasks, point to Restore, and then click Page, which opens the Restore Page dialog box.
   Click Verify to check the integrity of the backup files needed to perform the page restore operation.
4. Then to identify corrupted pages, with the correct database selected in the Database box, click Check Database Pages. This is a long-running operation. To restore specific pages that are not corrupted, click Add and enter the File ID and Page ID of the pages to be restored.
5. The pages grid is used to identify the pages to be restored. Initially, this grid is populated from the suspect_pages system table. To add or remove pages from the grid, click Add or Remove.
6. Now the Backup sets grid lists the backup sets in the default restore plan. Optionally, click Verify to verify that the backups are readable and that the backup sets are complete, without restoring them.
7. Finally, to restore the pages listed in the pages grid, click OK.

Using Transact-SQL

To specify a page in a RESTORE DATABASE statement, we need the file ID of the file containing the page and the page ID of the page. The required syntax is as follows:

RESTORE DATABASE <database_name>
PAGE = ‘<file: page> [ ,… n ] ‘ [ ,… n ]
FROM <backup_device> [ ,… n ]
WITH NORECOVERY

To restore pages

1. First identify the damaged page id from Error logs, Event traces, DBCC CheckDB command, WMI Provide, etc. (A CHECKSUM or torn write error returns page ID to perform PAGE level restore)
2. Start a PAGE level restore with a FULL or FILEGROUP backup that contains the page. You can use the PAGE clause in the RESTORE DATABASE command and specify all the PAGE IDs of all the pages to be restored. You can only restore 1000 pages per one RESTORE command.
3. Apply the most recent differentials required for the pages to restore.
4. Apply the subsequent log backups if any.
5. Create a new log backup of the database that includes the final LSN (Log Sequence Number) of the restored pages, that is, the point at which the last restored page is taken offline. The final LSN, which is set as part of the first restore in the sequence, is the redo target LSN. Online roll forward of the file containing the page is able to stop at the redo target LSN.
6. Restore the new log backup. It then it applies the new log backup and completes the page restore. This sequence is analogous to a file restore sequence. In fact, we can perform both page restore and file restores as part of the same sequence.

[Need any further assistance in fixing SQL errors? – We’re available 24*7]

Conclusion

In short, during a single database page damage users want to restore the individual pages without affecting the entire database. Today, we saw how our Support Engineers fix this error.