Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
When the database falls into the suspect mode, we can solve this problem by restoring only the corrupted pages instead of restoring full backup. You can find suspect pages with the help of the following script.
1 | select * from msdb..suspect_pages |
The suspect_pages table may change after the following situations.
- When a query want to read a data
- After the DBCC CHECKDB command is executed
- After backup is taken
Let’s confirm the pages we will restore using the following script.
1 | DBCC CHECKDB (N'Test') WITH NO_INFOMSGS, ALL_ERRORMSGS; |
After this process, let’s run the following script again.
1 | select * from msdb..suspect_pages |
Let’s try to get a tail log backup with the help of the following script from the database that was falled to the suspect mode before starting the Restore process. Tail Log Backup takes the backup of the log records after the last log backup. You should not use NO_TRUNCATE in a healthy database.
1 2 3 | BACKUP LOG Test TO DISK = 'C:\MSSQL\TestTailLog.trn' WITH NO_TRUNCATE, COMPRESSION, STATS = 1 |
After finding the suspect pages, we write file_ids and Page IDs to the relevant sections in the script below.
| 1 | file id |
| 58 and 200 | page ids |
1 2 3 4 5 6 7 | use master go RESTORE DATABASE Test PAGE='1:58, 1:200' FROM DISK=N'C:\MSSQL\testfull.bak' WITH NORECOVERY ; RESTORE LOG Test FROM DISK=N'C:\MSSQL\testlog.trn' WITH NORECOVERY; |
1 2 | RESTORE LOG Test FROM DISK=N'C:\MSSQL\TestTailLog.trn' WITH RECOVERY; |
We’ll enter the page or pages that we’ll restore by clicking Add in the Pages screen. Of course, the backups that appear in the “backup sets” section on the bottom side must be in those paths. You can confirm the accuracy of those files before performing the restore operation by clicking Verify.
