Assuming dbcc checkdb shows corruption on 1 page.
Suppose I want to fix this by restoring the corrupted page only.
So I go to the Restore page UI, click the button to find the corrupted page, enter the full and log backup files and restore.
My question is - what is the way that sql sever checks whether the page is corrupt or not in the given full backup?
It is the responsibility of the DBA to have an adequate maintenance plan in place and to ensure the integrity of the running database and the database backups.
DBCC CHECKDB() is the tool to check for corruption in the productive running database.
BACKUP DATABASE ... WITH CHECKSUM... would be an adequate setting to ensure that the data written to the backup location is the same as the data that was retrieved from the running database.
Specifies that the backup operation verifies each page for checksum and torn page, if enabled and available, and generate a checksum for the entire backup.
Setting the Option Verify Backup in the GUI or performing a manual RESTORE VERIFYONLY... against the database backup file, will also check the validity of the created backup file.
RESTORE DATABASE ... WITH CHECKSUM ... is what you would run to restore the database to a an alternate location / server.
By default, on encountering an invalid checksum, RESTORE reports a checksum error and stops. However, if you specify CONTINUE_AFTER_ERROR, RESTORE proceeds after returning a checksum error and the number of the page containing the invalid checksum, if the corruption permits.
After the restore, you would then again run a DBCC CHECKDB() to ensure that the restored database is not corrupt.
What is the SQL server mechanism to ensure that the page being restored isn't corrupted?
There is none. There are only a couple of steps (outlined above) that a DBA has to perform to ensure the database backup is not corrupt. A page can contain corruption and still be restored. Only after running a DBCC CHECKDB() will you know if a page is corrupt or not.
It wont. If you have a corrupted page in a backup file and you restore it, SQL will restore it (and it won't raise any error).
It's your job to run checkdb frequently and make sure you do not backup corrupted pages.
For now, you could try restoring your latest full backup + diff/log and run a checkdb to see if this fixed your corruption.
If it did not, then you can get the previous full backup and apply all log up to the last (which may take time as you may have a lot of them to restore) and run a checkdb again.
*You could save time by restoring only the full somewhere else and run the checkdb there. If the page is not corrupted in the full, it should technically remain uncorrupted after applying the logs.
