Page Restore On SQL Server

dbtut

6 years ago

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.

select * from msdb..suspect_pages

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.

DBCC CHECKDB (N'Test') WITH NO_INFOMSGS, ALL_ERRORMSGS;

1	DBCC CHECKDB (N'Test') WITH NO_INFOMSGS, ALL_ERRORMSGS;

After this process, let’s run the following script again.

select * from msdb..suspect_pages

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.

BACKUP LOG Test
TO DISK = 'C:\MSSQL\TestTailLog.trn'
WITH NO_TRUNCATE, COMPRESSION, STATS = 1

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

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;

use master

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;

Finally, we restore the tail log backup in recovery mode and complete the process.

RESTORE LOG Test FROM DISK=N'C:\MSSQL\TestTailLog.trn' 
WITH RECOVERY;

1 2	RESTORE LOG Test FROM DISK=N'C:\MSSQL\TestTailLog.trn' WITH RECOVERY;

Page Restore can also be performed with SQL Server 2012 via SSMS. Right-click on the appropriate database, and then click Task-restore-page.

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.