Tips and Tricks: SQL Database Recovery

Author:

March 2009

Situation
Your SQL Server database is in suspect mode. You notice errors like the following:
Error: 823, Severity: 24, State: 2 I/O error detected during at offset in file ''

Problem
You have no valid backups

Solution
Recover the corrupt database
Torn I/O Overview
Torn I/O is often referred to as a torn page in SQL Server documentation. A torn I/O occurs when a partial write takes place, leaving the data in an invalid state. SQL Server 2000 data pages are 8 KB in size. A torn data page for SQL Server occurs when only a portion of the 8 KB is correctly written to or retrieved from stable media.
SQL Server always checks I/O completion status for any operating system error conditions and proper data transfer size and then handles errors appropriately. Torn pages generally arise after system outages where the subsystem does not complete the entire 8-KB I/O request.
Disk drive manufacturers generally limit data transfer guarantees to sector boundaries of 512 bytes, so if the stable media is a physical disk and the controller does not contain a cache with battery backup capabilities, the I/O request is limited to the final spin/transfer rate of the physical drive. Thus, if an I/O is issued to write 8 KB (for a total of sixteen 512-byte sectors), but only the first three sectors make it to stable media, then the page becomes torn resulting in data corruption. A subsequent read of the 8-KB page would bring in 3 sectors of the new version of the page and 13 sectors of an older version.

Steps for a successful database recovery

•    Database Recovery

1.    Set the database to the allow updates and reconfigure with override
2.    Create a database with an mdf of the same size as the old one  then stop SQL Server
3.    Copy the corrupt mdf over the newly created one then restart SQL Server
4.    Set the database status to 32768
5.    Transfer data to another database


•    Data Integrity

Once all data has been transferred to new database, perform the following steps

1.    Backup the new database
2.    Add logins
3.    Check for duplicate rows
4.    Check and rebuild indexes
5.    Verify views and stored procedures
6.    Drop old database and rename new database

More Tips

•    Always verify your backups
•    You can transfer all data up to and around the torn page
•    Transfer data to a new server then backup and restore to original server
•    When transferring data you can use BCP, DTS or queries
•    You can script your old and new database to compare differences
•    Start a backup maintenance plan

If you need more information, contact Gary Codeluppi at 937-431-1026 x1123 or visit the Ross Group Inc web site at www.rossgroupinc.com.