Database corruption can be difficult to detect and sometimes it is very possible that you might not even notice it until you run DBCC command on your database to check logical and physical consistency of that database. It is very common practice to run DBCC CHECKDB without any additional options at regular intervals to make sure that database isn’t corrupted. Whole database corruption recovery process is a bit tricky process and there are many different factors effect steps of recovery. And I think it takes lots of time and experience for someone to be very good at database corruption recovery (and I can say that I am not even 1/2 good at it, but I am learning). In many cases if you find corruption easiest way is to just restore “good” copy of database, but this might not work if you have this “good” copy years old .. or worse you don’t even have that “good” copy …. and based on your case recovery process will very largely.
The point of this post is to show an addition of new feature introduced in SQL Server 2012 which will surely help greatly in process to recover individual page of database. But before we try that you need to have a corrupt database 
.. naturally. For this I found this nice little post about how to corrupt database by Kendra Little. Just make sure to take backup before corrupting database to simulate real life situation (which again, not always stands true).
First the old way recovery …..
In Pre-2012 systems you can restore a specific page by using t-sql commands only. So restore that corrupt page I had to use following sql query, (based on results from DBCC CHECKDB WITH PHYSICAL_ONLY)
USE [master]
RESTORE DATABASE [CorruptMe] PAGE=’1:2784′ FROM DISK = N’C:\temp\Backup1.bak’ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
BACKUP LOG [CorruptMe] TO DISK = N’C:\temp\CorruptMe_LogBackup_2012-07-07_15-24-47.bak’ WITH NOFORMAT, NOINIT, NAME = N’CorruptMe_LogBackup_2012-07-07_15-24-47′, NOSKIP, NOREWIND, NOUNLOAD, STATS = 5
RESTORE LOG [CorruptMe] FROM DISK = N’C:\temp\CorruptMe_LogBackup_2012-07-07_15-24-47.bak’ WITH NOUNLOAD, STATS = 5
Post 2012 way recovery …
Once you have corrupted database you just need to fireup SSMS (SQL Server Management Studio) 2012 and connect to instance of SQL Server which has corrupt database. Then, right click on database and go to Tasks –> Restore –> Page this will open up GUI to recover page. May be in near future we will see more improvements but even with limitations this UI looks pretty useful. It can automatically detect corrupted pages and in addition to that you can add any # of pages that you want to recover.
Actually it executes same script as above in background.
As I mentioned before that corruption recovery process is very custom recovery process and it is possible that it will not be same for all situations. I can see that this page recovery option exists if I connect to Pre-2012 instance using SSMS 2012 but I am feeling lazy to test recovery process for older versions but I think it should work just fine.
On a side note, if you have check sum verify option enabled in your database and while taking backup if you select option “Perform Checksum before writing to media” then while taking backup you will encounter checksum error which is an indication of database corruption (and a good way to prevent database corruption go too deep into database backups)
Like I mentioned database corruption is a bit messy subject and it is not easy to write about it in generalized form since solution depends on number of factors. So, that’s it for now …
It’s Just A Thought …
