{"id":1908,"date":"2020-09-21T07:00:00","date_gmt":"2020-09-21T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=1908"},"modified":"2026-03-23T17:34:57","modified_gmt":"2026-03-23T14:34:57","slug":"pos-kanoyme-sygkekrimeno-mono-page-restore-ston-sq","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-kanoyme-sygkekrimeno-mono-page-restore-ston-sq\/","title":{"rendered":"How do we restore a specific page only in SQL Server"},"content":{"rendered":"

In the article we will analyze what we can do in the case of page corruption in SQL Server. It is possible to restore only the specific page and avoid restoring the entire database through the backups we have (as long as the database is in full recovery model). <\/p>\n\n\n\n

Our initial data<\/h5>\n\n\n\n

We have created the table named \u201cpinakas\u201d in the database \u201cdb_test\u201d and it has passed several records that are written on different pages:<\/p>\n\n\n\n

select * from db_test..pinakas<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Let's take a full backup, if we had an older one we could use this too:<\/p>\n\n\n\n
BACKUP DATABASE [db_test]\n TO  DISK = N'C:\\backups\\db_test_full.bak' WITH INIT\n, COMPRESSION,  STATS = 10\nGO<\/pre>\n\n\n\n
On which pages the table data is stored<\/h5>\n\n\n\n
DBCC IND(db_test, pinakas, -1)<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
How to see what exactly is stored inside the page<\/h5>\n\n\n\n
dbcc traceon(3604)\ndbcc page(db_test,1,392,3) with tableresults<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Time to corrupt a table page ourselves<\/h5>\n\n\n\n
With the DBCC WRITEPAGE command we will cause our corrupt with the data we have from the previous query by changing and filling in the database name and the page number (in our example 392):<\/p>\n\n\n\n
ALTER DATABASE db_test SET SINGLE_USER WITH ROLLBACK IMMEDIATE\nGO\nDBCC WRITEPAGE(db_test, 1, 392, 0, 1, 0x41, 1)\nDBCC WRITEPAGE(db_test, 1, 392, 1, 1, 0x41, 1)\nDBCC WRITEPAGE(db_test, 1, 392, 2, 1, 0x41, 1)\nGO\nALTER DATABASE db_test SET MULTI_USER\nGO<\/pre>\n\n\n\n
After we have caused the corruption let's go to the table and another record<\/h5>\n\n\n\n
As if the corruption we caused wasn't enough, let's pass another record after the corruption that we don't want to lose:<\/p>\n\n\n\n
INSERT INTO db_test.dbo.pinakas (onoma,tilefono,epitheto) VALUES ('Agamemnonas','693333333',null)\nGO<\/pre>\n\n\n\n
*I noticed that unlike the older versions in SQL Server 2019 it doesn't let you pass a new record if you have corruption in the table.<\/em> This certainly makes the recovery process easier, but as we may have an older version, we consider for the need of the article that there is a record passed after the corruption.<\/em><\/p>\n\n\n\n
What if we now try to see the table<\/h5>\n\n\n\n
select * from db_test..pinakas<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
It clearly tells us that something is wrong and we cannot access the table.<\/p>\n\n\n\n
How do we find exactly where corruption has occurred?<\/h5>\n\n\n\n
For starters, running checkdb <\/strong>we will see that the problem is in this particular table:<\/p>\n\n\n\n
dbcc checkdb (db_test)<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
through her view msdb <\/strong>we see which page has become corrupt:<\/p>\n\n\n\n
select * from msdb.dbo.suspect_pages<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
How will we see what backups we have taken as a basis to see what we will do<\/h5>\n\n\n\n
SELECT [media_set_id]\n   ,[media_count]\n   ,[physical_device_name]\n   ,[device_type]\n   ,[physical_block_size]\n FROM [msdb].[dbo].[backupmediafamily]\n<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Restoring the corrupted page<\/h2>\n\n\n\n
For starters we should get one transaction log backup<\/strong> which will contain the information from what has been done in the database since the last backup:<\/p>\n\n\n\n
BACKUP LOG db_test TO\n\tDISK = 'C:\\backups\\db_test_logb.bak'\n\tWITH INIT\nGO<\/pre>\n\n\n\n
Then we perform the classic restore database<\/strong> with the but parameter of page <\/strong>so that only the specific one is restored. During the whole process we do not recover the base, so we put the parameter with<\/strong> norecovery <\/strong>so that no new data can be added to the database:<\/p>\n\n\n\n
RESTORE DATABASE db_test\n\tPAGE = '1:392'\n\tFROM DISK= N'C:\\backups\\db_test_full.bak'\n\tWITH NORECOVERY\nGO<\/pre>\n\n\n\n
Now that the base has been restored with norecovery it is not accessible, <\/strong>this means that nothing new can be passed to the base. So to be 100% sure that we won't lose information, we call one tail log backup <\/strong>that is, a transaction log backup while the base it is not recovered<\/strong> (accessible):<\/p>\n\n\n\n
BACKUP LOG db_test TO\n\tDISK = 'C:\\backups\\db_test_LOG_TAIL.bak'\n\tWITH NORECOVERY,INIT\nGO<\/pre>\n\n\n\n
We restore the last transaction log we got:<\/p>\n\n\n\n
** Of course, if other transaction log backups have been taken since the last full \/ differential backup, they must be restored first in chronological order<\/em>.<\/p>\n\n\n\n
RESTORE LOG db_test FROM\n\tDISK = 'C:\\backups\\db_test_logb.bak'\n\tWITH NORECOVERY\nGO<\/pre>\n\n\n\n
Lastly, the tail log backup we took is restored:<\/p>\n\n\n\n
RESTORE LOG db_test FROM\n\tDISK = 'C:\\backups\\db_test_LOG_TAIL.bak'\n\tWITH NORECOVERY\nGO<\/pre>\n\n\n\n
After we are done with the restores, call the command with recovery so that the database becomes accessible again:<\/p>\n\n\n\n
RESTORE DATABASE db_test WITH RECOVERY\nGO<\/pre>\n\n\n\n
The result<\/h2>\n\n\n\n
Now if we try to select the table, we will see the records that were there plus the one that passed after the corruption:<\/p>\n\n\n\n
select * from db_test..pinakas<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Sources:<\/h2>\n\n\n\n