SQL Server Page Level corruption is encountered due to several reasons, which includes hardware related issues, i.e. failure of certain hardware, hard disk functioning failure, improper network establishment, or logical level of issues such as malware intrusion on the Server storing data, repository being declared as out of limit. These conditions are what arises erroneous messages while working on the server database resulting in distortion.
The discussed conditions result in SQL Server page level corruption and are responsible for producing error 823 too. Thereafter, SQL begins to execute its I/O level of operations from Windows API (Application Programming Interface). Server checks on every API and instantly reports error on finding inconsistency.
Performing Page Restore – The Right Time
Page restore is a procedure that is meant for fixing the isolated pages when in damaged condition. Restoring or recovering a countable number of pages is instantly executable as compared to a complete file restore. The procedure reduces amount of data, which is offline at the time of restoration. Nevertheless, when restoring damaged pages in bulk from a database file, restoring the entire Database is rather suggested. Suppose, several pages on a device are pointing a pending failure of device, one must consider immediately restoring the file. This, if done on a different location would be highly effective, as it will give better chance of repairing the device too.
Moreover, restore is not necessarily the option for all types of page related errors. The problem can also occur within the cached copy of the data like an alternate index and such problem can be fixed with the simple recalculation of data. For instance, admin of the database drops in a secondary index and reconstructs it. Though the corrupted data is fixed, it isn’t specified within the table – suspected_pages.
How To Perform A Page Restore?
Diagnosis of SQL Server page-level corruption is the primary step of performing a restore. For dealing with SQL Server database corruption, most businesses are already prepared with contingency plans. Otherwise, any Server Pro would instantly be ready with a number of strategies to tackle the condition at hand. DR plans, DB mirroring, replication, AlwaysOn, etc., are some of the common strategies that any pro or admin would immediately be prepared with. However, these may not be the applicable in all conditions.
In case of an error, it is clear enough that the integrity of an SQL Server DB is compromised with and thus needs to be fixed. Regardless of what the problem is, the first solution that comes to mind is – Restore. However, in the scenario of a SQL Server page-level corruption the restore is classified into several categorizations and those include the following:
- Setting the database to a single-user mode
- Tail the log database backup for all transactions carried out since the last log backup
- Perform a full backup restore from the current date
- Restore 4 transact log files every hour
- Restore the tail log backup till the date of corruption
- Perform the check on database using DBCC Check
Limitations of a Page Restore
Although a page restore can be executed in the case of damaged file pages, but there are certain limitations that may confine you from doing so.
- Only those databases that are using any of the two recovery models, i.e. bulk-logged or full, are only suitable for a page restore process. Only read/write filegroups support page restore.
- Page restore only applies to database pages and not the following:
- Full text based catalog
- Transaction log database
- Page 1:9 which denotes the database boot page
- Page 0 of any/all data files as it is the file boot page
- And the allocation pages, i.e. – GAM, SGAM, and PFS
- The following restoration conditions must be met by databases using the bulk-logged recovery model type:
- Page level restore in most cases fails to work with bulk-logged recovery model of database.
- Unless WITH ‘CONTINUE_AFTER_ERROR’ condition is specified, it is possible that the log backup file maintained for a bulk-logged recovery type database when encounters bad page fails.
TIP: The best practice while carrying out a page restore process is to assign the database to full recovery model and then attempt generating a log backup. In case the backup works well then it is safe to continue carrying out a page restore, otherwise one must avoid. In case of backup failure, you are supposed to face either of the following conditions:
- Loss of work that was done post the last log backup
- Alternatively, you could try DBCC with the REPAIR_ALLOW_DATA_LOSS syntax with the major risk of losing corrupt data in the process.
Discussed above is a detailed explanation of the SQL Server page-level corruption in database and its resolution. However, the limitations and conditions applicable are also significant to be noticed beforehand to attain desirable output and guaranteed solution. Only a negligible number of pages, when damaged, must be considered the right condition for performing a page level restore. In case of bulk of pages, a manual procedure needs to be carried out as 2012 and previous SSMS GUI did not permit performing page level restoration. In that case, a full recovery needs to be performed as the only resort left. But this process can be time-consuming, so it is better to use SQL Database Repair for quickly sort out the problem.