Disaster recovery has been one of my favorite topics when it comes to SQL Server. Most likely this stems from the time I spent as a disaster recovery analyst, when my sole purpose was to backup and restore. I realized very quickly that everything can’t always be restored, but with proper knowledge and preparation, you can recover to as close to the original state as possible. This line of thinking has truly influenced me as a DBA and drives me to develop my skills at recovering data quickly and effectively. In my studies, I came across an article, which you can read here, about restoring pages in SQL Server 2012. Where I work we don’t have many 2012 instances, but we do have quite a bit of 2008 R2 instances; the article I read made me wonder if this was also possible in 2008 R2. There was no better way to figure this out than to test it myself. The question was how I would test this since I didn’t have any databases with corrupt pages. I created corruption by turning to my trusty hex editor HxD.
First of all, I created a database and table to use in my tests. I populated the table with some data and took a full backup of the database. I then detached the database and opened the mdf file in HxD. I scrolled down to somewhere near the middle of the window and replaced a line of text with “Killed this database.”
I then proceeded to save the file and reattach the database. Everything looked fine; the database attached with no issue. Wondering if it worked, I ran DBCC CHECKDB to see, and the database was, in fact, corrupt. The output was very helpful in figuring out what pages were corrupt.
The output stated “Page (1:153) could not be processed.” I guessed that would be a good place to start, so I ran the restore script seen below.
The output of the above script is: The roll forward start point is now at log sequence number (LSN) 30000000046300001. Additional roll forward past LSN 31000000004300001 is required to complete the restore sequence.
RESTORE DATABASE … FILE=<name> successfully processed 1 pages in 0.042 seconds (0.186 MB/sec).
The output means you need to backup the transaction log with the norecovery option and then restore it back with recovery.
I executed a final DBCC CHECKDB, and there was no corruption. Hooray!!
Now, it is important to note that this type of restore will not work on the boot section of any data file, transaction log files, or a full-text catalog as noted in the aforementioned article.