Fix Page Level Corruption of SQL Server Database

Database corruption can occur anytime and most often due to faulty hardware or operational mistakes like deleting the transactional log file etc. SQL server database has a RESTORE DATABASE command, which has an optional parameter. It provides the facility to restore one or more pages instead of the entire database. This option is very useful when corruption occurs in few database pages by IO subsystem problem.To solve this problem you can try these options:

• First of all, change the database into single user mode because end users can create the restrictions in the restore operation. To set the database into SINGLE_USER mode, execute the following command:

ALTER DATABASE <MyDatabaseName> SET SINGLE_USER

WITH ROLLBACK AFTER 10 SECONDS

GO

Here we use the ROLLBACK AFTER n SECONDS option, which will be able to kill the running operations and connection.

• Now you can start the recovery process by taking the tail-end backup of the log file. It can be a single portion that has not already backed up. Example :

BACKUP LOG MyDatabaseName

TO DISK = N'D:\SQLBackups\ MyDatabaseName _TailEnd.trn'

GO

• Now, execute the RESTORE DATABASE command with PAGE switch option which is loaded as many pages as needed.

RESTORE DATABASE MyDatabaseName

PAGE = 'fileid:pageid,fileid:pageid,etc'  -- e.g. 1:5220,1:5221,etc

FROM DISK = 'D:\SQLBackups\ MyDatabaseName _lastFull.BAK'

WITH

NORECOVERY

GO

Here we are using NORECOVERY option so; you do not recover your database with this command.

• You can apply the transactional log with NORECOVERY either you have full backup or differential backup.  Make sure, you do not recover the database. See example:

RESTORE LOG MyDatabaseName

FROM DISK = 'D:\SQLBackups\ MyDatabaseName_LogFileFrom3PM.TRN'

WITH NORECOVERY

GO

RESTORE LOG MyDatabaseName

FROM DISK = 'D:\SQLBackups\ MyDatabaseName_LogFileFrom315PM.TRN'

WITH NORECOVERY

GO

There are similar log backups in the sequence. This sequence runs up to the point of disaster. 

RESTORE LOG MyDatabaseName

FROM DISK = 'D:\SQLBackups\ MyDatabaseName_LogFileFrom630PM.TRN'

WITH NORECOVERY

GO

Now again we are using NORECOVERY option.

• Now we will apply the tail-end backup that we have got in step 2 and recover the database.

RESTORE LOG MyDatabaseName

FROM DISK = ‘D:\SQLBackups\ MyDatabaseName _TailEndBackupOfYourLogFileFromStep2.TRN’

WITH RECOVERY

Note: Here we are using the recovery option to recover the database.

• Now use the following command to clear out the msdb..suspect_pages

DELETE FROM msdb..suspect_pages

GO

• Now, again run the DBCC CHECKDB() with ALL_ERRORMSGS (and NO_INFOMSGS) options.
• Switch the database into the multi-user mode:

ALTER DATABASE MyDatabaseName SET MULTI_USER

GO

Final Words: We have seen the steps of page level restore options. This is more feasible for few page corruptions. If you have a large number of corrupt pages then you have to manually handle all t-log applications. A third party tool is also available to fix the page level corruption.