Website owners, webmasters and organizations sometimes want to do a page level restore in SQL server.
The process of performing a page level restore in SQL Server is a prudent way of restoring the entire database when backup is not required.
As part of our Server Support Services, we have handled numerous SQL databases restoration and backup processes for our customers.
In this context, we shall discuss the processes involved in doing a page level restore successfully.
Why page level restore in SQL server is essential?
Sometimes, some part of a database gets corrupted. It may seem that a total restoration of the database is the only way to get the System or website back online.
Making a complete restoration of the database will result in overwriting of all its old content and this is not always preferable. Thus a page level restore is better in some cases.
The reason why this approach is used is that it helps to fix one or more corrupted pages from previous backups.
You can carry out Page level restore through T-SQL or SSMS.
Page level restore via SQL Server Management Studio (SSMS)
Follow the following steps to do page level restore in SSMS;
i. To begin, Connect to the SQL Server Database Engine instance in the Object Explorer where you will see the name of the server. Click the Server name to see the server tree.
ii. In the Databases tab, choose either a user database or System Databases depending on the one you want to work with.
iii. Right-click on the database, go to Tasks, then Restore, and click on Page to show the Restore Page dialog box. Then click Verify to check the integrity of the backup files which will be used in the page restore process.
iv. Detect the corrupted pages by selecting the database concerned in the Database box after which you should click Check Database Pages. This process will take some time. Restoring specific pages which are not corrupted can be done by clicking Add and input the File ID as well as the Page ID of the pages that need to be restored.
v. In the pages grid, click Add or Remove to add or remove pages from the grid that needs to be restored.
vi. Now you can restore the pages contained in the pages grid by clicking OK.
Page level restore via Transact-SQL (T-SQL)
Performing page restore via this method means that you must identify the file ID of the page and the page ID to be restored. The RESTORE DATABASE statement below can be used to complete this process;
RESTORE DATABASE <database_name>
PAGE = ‘<file: page> [ ,… n ] ‘ [ ,… n ]
FROM <backup_device> [ ,… n ]