I am working with SQL Server 2008 64-bit (10.0.2531) on Windows Server Datacenter 2008 SP2 (64-bit).
The problems I am having almost certainly stem from my lack of understanding of how SQL Server's transaction log is handled during recoveries. I am using
this document as a rough guide.
Here' what I am doing:
1. I intentionally corrupt a single data page in a normal table, so that I can practice single-page recovery.
2. I attempt to read the corrupt block in SQL. As expected I receive an error.
1> select top 10 firstname from ds2.dbo.customers
Msg 824, Level 24, State 2, Server IP-0AF647E0, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 4:896; actual 3:0).
It occurred during a read of page (4:896) in database ID 5 at offset 0x00000000700000 in file 'C:\sql\dbfiles\cust1.ndf'.
Additional messages in the SQL Server error log or system event log may provide more detail.
3. I take a log tail backup before I do anything to try to fix the corruption:
1> backup log ds2 to disk = 'c:\sqlbackup\ds2.bak' with norecovery
Processed 5 pages for database 'ds2', file 'ds_log' on file 1.
BACKUP LOG successfully processed 5 pages in 0.041 seconds (0.857 MB/sec).
4. I restore the corrupt page from backup:
1> restore database ds2 page='4:896'
2> from disk='c:\sqlbackup\ds2.bak'
3> with norecovery
View Complete Post