How do we restore a specific page only in SQL Server
Certifications:
- How we enable Unified Auditing in Oracle Database - 7 October 2024
- What is PostgreSQL and how do we do a full installation - September 2, 2024
- How do we configure GoldenGate replication to read from Oracle Data Guard Standby - 2 August 2024
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).
Our initial data
We have created the table named “pinakas” in the database “db_test” and it has passed several records that are written on different pages:
select * from db_test..pinakas
Let's take a full backup, if we had an older one we could use this too:
BACKUP DATABASE [db_test] TO DISK = N'C:\backups\db_test_full.bak' WITH INIT , COMPRESSION, STATS = 10 GO
On which pages the table data is stored
DBCC IND(db_test, pinakas, -1)
How to see what exactly is stored inside the page
dbcc traceon(3604) dbcc page(db_test,1,392,3) with tableresults
Time to corrupt a table page ourselves
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):
ALTER DATABASE db_test SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO DBCC WRITEPAGE(db_test, 1, 392, 0, 1, 0x41, 1) DBCC WRITEPAGE(db_test, 1, 392, 1, 1, 0x41, 1) DBCC WRITEPAGE(db_test, 1, 392, 2, 1, 0x41, 1) GO ALTER DATABASE db_test SET MULTI_USER GO
After we have caused the corruption let's go to the table and another record
As if the corruption we caused wasn't enough, let's pass another record after the corruption that we don't want to lose:
INSERT INTO db_test.dbo.pinakas (onoma,tilefono,epitheto) VALUES ('Agamemnonas','693333333',null)
GO
*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. 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.
What if we now try to see the table
select * from db_test..pinakas
It clearly tells us that something is wrong and we cannot access the table.
How do we find exactly where corruption has occurred?
For starters, running checkdb we will see that the problem is in this particular table:
dbcc checkdb (db_test)
through her view msdb we see which page has become corrupt:
select * from msdb.dbo.suspect_pages
How will we see what backups we have taken as a basis to see what we will do
SELECT [media_set_id] ,[media_count] ,[physical_device_name] ,[device_type] ,[physical_block_size] FROM [msdb].[dbo].[backupmediafamily]
Restoring the corrupted page
For starters we should get one transaction log backup which will contain the information from what has been done in the database since the last backup:
BACKUP LOG db_test TO DISK = 'C:\backups\db_test_logb.bak' WITH INIT GO
Then we perform the classic restore database with the but parameter of page so that only the specific one is restored. During the whole process we do not recover the base, so we put the parameter with norecovery so that no new data can be added to the database:
RESTORE DATABASE db_test PAGE = '1:392' FROM DISK= N'C:\backups\db_test_full.bak' WITH NORECOVERY GO
Now that the base has been restored with norecovery it is not accessible, 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 that is, a transaction log backup while the base it is not recovered (accessible):
BACKUP LOG db_test TO DISK = 'C:\backups\db_test_LOG_TAIL.bak' WITH NORECOVERY,INIT GO
We restore the last transaction log we got:
** Of course, if other transaction log backups have been taken since the last full / differential backup, they must be restored first in chronological order.
RESTORE LOG db_test FROM DISK = 'C:\backups\db_test_logb.bak' WITH NORECOVERY GO
Lastly, the tail log backup we took is restored:
RESTORE LOG db_test FROM DISK = 'C:\backups\db_test_LOG_TAIL.bak' WITH NORECOVERY GO
After we are done with the restores, call the command with recovery so that the database becomes accessible again:
RESTORE DATABASE db_test WITH RECOVERY GO
The result
Now if we try to select the table, we will see the records that were there plus the one that passed after the corruption:
select * from db_test..pinakas
