Steps to Perform Page Level Restore Operations In SQL Server

Steps to Perform Page Level Restore Operations In SQL Server

Many of organization work with sql server to maintain their huge amount of data.So its very much important to deal with any type of corruption case effectively.Here we learn how to restore the data base of one corrupt page.

Let us understand the steps involved in Page Level Restore Operation:

1.Keep SQL Server database into SINGLE_USER mode. We can keep the database to RESTRICTED_USER mode, but the end users or Applications connected, using logins that map to db_owner role will be able to connect to the database. Due to this, single user mode is taken and the query Window used to set this mode should not be closed. The command, given below, will be used to set the database to single user mode-

  1. ALTER DATABASE <DBNAME> SET SINGLE_USER
  2. WITH ROLLBACK AFTER 15 SECONDS
  3. GO

In the command, given above, WITH ROLLBACK AFTER n SECONDS option is used to end connections or on-going operations.

2.In the second step, we will start the recovery process by taking backup of the tail end of the log file or the portion of the log file, which was not backed up. We will use the command, given below-

  1. BACKUP LOG DBNAME
  2. TO DISK = N ‘E: \SQLBACKUPS\DBName_TailEnd.trn’
  3. GO

Note: This option is critical as it is used to make sure that all the operations up to a certain point have been accounted for. Before performing this operation, the database should be set to single user mode; else, it will miss some operations.

3. We will run the RESTORE DATABASE command with the PAGE switch and all the pages, which needs to be restored. It is necessary to start the operation with last FULL backup. Using the commands stated below, we will perform the restore operation-

  1. RESTORE DATABASE DBNAME
  2. PAGE = ‘1:3456,1:3457’
  3. FROM DISK = ‘E: \SQLBACKUPS\DBName_lastFull.bak’
  4. WITH NORECOVERY
  5. Go

In the command, mentioned above, PAGE defines the pages which need to be restored containing FileId1:PageId1, FileId2:PageId2, and so on. NORECOVERY mode should not be used during recovery of the database.

  1. RESTORE LOG DBName
  2. FROM DISK = ‘E: \SQLBackups\DbName_LogFileFrom1PM.trn’
  3. WITH NORECOVERY
  4. GO
  5. RESTORE LOG DBName
  6. FROM DISK = ‘E: \SQLBackups\DbName_LogFileFrom110PM.trn’
  7. WITH NORECOVERY
  8. GO
This command should be executed repeatedly up to the point of disaster. However, make sure, you do not recover the database, while doing so.
4. We will apply tail end backup, as the one, we did in step 2 and then we will now recover the database
  1. RESTORE LOG DBName
  2. FROM DISK = ‘D: \SQLBackups\DBName_TailEndBackupOfLogFileFromStep2.trn’
  3. WITH RECOVERY

We will delete all the msdb..suspect_pages, using the commands, given below-

DELETE FROM msdb..suspect_pages

Remember to delete the table before going to next step, since it is not automatically cleaned or running DBCC CHECKDB() command. It is done to ensure that there are no reports for bad-pages with the pages you have already corrected.
5. We will again use DBCC CHECKDB () command against the database to ensure the use of ALL_ERRORMSGS options.
6. Bring back the database from single_user mode to MULTI-USER mode to keep the database open for production usage, using the commands, given below-
  1. ALTER DATABASE DBName SET MULTI_USER
  2. GO

If there are any issues, user can try clearing the msdb..suspect_pages again, if necessary.

While working with large-sized databases, every SQL Server Database administrator needs to be aware of page level restore operations. It is very helpful if there is a small number of pages that are damaged, as it saves users from restoring complete database. Though this approach provides an effective restore function but it will a require lot of manual scripting of transaction-log file restoration operations, which can be tiresome. The blog will act as a guide for those users or administrators, who wish to perform the page-restore operations in SQL Server Database without the loss of any data.

Leave a Reply

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