2

TL;DR: How can I fix the relationship between an MSSQL database and the associated FILESTREAM files for a database that was recovered from failed disks?

Background

A server hosting a FILESTREAM-enabled database suffered a catastrophic disk failure and there are no usable backups (yes, I know, but it was out of our control at the time). The data has been recovered by a third party which indicated there were no errors in the data, log or FILESTREAM files. The files may have previously suffered some logical damage due to unexpected reboots, BSODs, etc. probably due to I/O errors.

I have sucessfully re-attached the recovered files (created on SQL 2008 Express) to a SQL 2008 R2 Express instance with the following query:

USE [master]
GO
CREATE DATABASE [DatabaseName] ON 
(FILENAME = N'C:\PathTo\DatabaseName.mdf' ),
(FILENAME = N'C:\PathTo\DatabaseName_log.ldf' ),
FILEGROUP [FileStreamGroup] CONTAINS FILESTREAM DEFAULT 
(NAME = N'DatabaseName_FSData', FILENAME = N'C:\PathTo\DatabaseNameFSData')
FOR ATTACH
GO

Problem

When I query one of the FILESTREAM enabled tables I get the following error:

Msg 824, Level 24, State 2, Line 2
SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x00000000; actual signature: 0x532f0224). It occurred during a read of page (1:4070) in database ID 20 at offset 0x00000001fcc000 in file 'C:\Polimex\Polimex.mdf'.

When I run DBCC CHECKDB('DatabaseName') WITH PHYSICAL_ONLY I get the following errors (removed dozens of duplicates):

DBCC results for 'DatabaseName'.
Msg 8909, Level 16, State 1, Line 7
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -9223089458054627328 (type Unknown), page ID (1:2454) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
...
Msg 8939, Level 16, State 98, Line 7
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -8981458780183265280 (type Unknown), page (21118:-260756540). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -14.
...
Msg 8909, Level 16, State 1, Line 7
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -3491674610548080640 (type Unknown), page ID (1:6074) contains an incorrect page ID in its page header. The PageId in the page header = (22311:1975551487).
...
Msg 8939, Level 16, State 98, Line 7
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -2878919214903066624 (type Unknown), page (32773:-570425347). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -1.
...
...
CHECKDB found 0 allocation errors and 129 consistency errors not associated with any single object.
...
Msg 8928, Level 16, State 1, Line 7
Object ID 128719511, index ID 1, partition ID 72057594060996608, alloc unit ID 72057594063159296 (type LOB data): Page (1:4006) could not be processed.  See other errors for details.
...
Msg 8939, Level 16, State 98, Line 7
Table error: Object ID 128719511, index ID 1, partition ID 72057594060996608, alloc unit ID 72057594063421440 (type In-row data), page (1:6055). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
...
CHECKDB found 0 allocation errors and 42 consistency errors in table 'TableName' (object ID 128719511).
CHECKDB found 0 allocation errors and 278 consistency errors in database 'DatabaseName'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

When I run DBCC CHECKDB ('DatabaseName', REPAIR_ALLOW_DATA_LOSS) I get the following additional errors:

Msg 8909, Level 16, State 1, Line 4
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -9223089458054627328 (type Unknown), page ID (1:2454) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Repairing this error requires other errors to be corrected first.
...
Msg 7908, Level 16, State 1, Line 4
Table error: The file "00000059-00000185-0003" in the rowset directory ID a1dac0db-a693-4093-897a-4b9bf9d9ff81 is not a valid FILESTREAM file.
The system cannot self repair this error.

AFAIK, the second error indicates that a file exists that is not tied to a data value in the database. I have checked the FSData folder and found the referenced file, however, it appears in two separate paths. It's possible that the second path was from a previous database that no longer exists, but this is where I'm stuck.

Bottom Line

How can I

  1. determine which files are the correct ones, and which ones can/should be removed from the directory structure to repair the database?
  2. confirm that the link between the files and the FILESTREAM-enabled columns is not broken?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.