Overview
As many organizations use SQL Server database to maintain their huge amount of data, it is very much necessary to be able to deal with the
corruption cases in SQL Server effectively. Let us suppose that there is a corrupt page in SQL Server database. Restoring the complete database for one corrupt page in the database is not a clever thing to do. Instead, we should find a solution, which can restore the page from the most recent database backup. In the blog, we will provide the solution on how to perform page level restore operations in SQL Server without any data loss.
Steps involved in Page Level Restore Operations
The users of SQL Server can use RESTORE DATABASE Command, which has an optional parameter, which instructs it to only restore one/more pages, instead of the entire database. It is helpful, when the user needs to deal with a few pages, which have been corrupted by the problems related to IO subsystem. 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-
- ALTER DATABASE <DBNAME> SET SINGLE_USER
- WITH ROLLBACK AFTER 15 SECONDS
- 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-
- BACKUP LOG DBNAME
- TO DISK = N ‘E: \SQLBACKUPS\DBName_TailEnd.trn’
- 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-
- RESTORE DATABASE DBNAME
- PAGE = ‘1:3456,1:3457’
- FROM DISK = ‘E: \SQLBACKUPS\DBName_lastFull.bak’
- WITH NORECOVERY
- 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.
- RESTORE LOG DBName
- FROM DISK = ‘E: \SQLBackups\DbName_LogFileFrom1PM.trn’
- WITH NORECOVERY
- GO
- RESTORE LOG DBName
- FROM DISK = ‘E: \SQLBackups\DbName_LogFileFrom110PM.trn’
- WITH NORECOVERY
- 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.
- RESTORE LOG DBName
- FROM DISK = ‘D: \SQLBackups\DBName_TailEndBackupOfLogFileFromStep2.trn’
- 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-
- ALTER DATABASE DBName SET MULTI_USER
- GO
7. If there are any issues, user can try clearing the msdb..suspect_pages again, if necessary.
Conclusion
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.