2

I am trying to recover a couple dev-dbs where the data directory was on a spanned partition that was inadvertently deleted. We were able to recover the folders/files using a recovery software, however the actual MDF file seems to have some issues.

Any attempt to attach or repair will fail with the following:

SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x00000000; actual signature: 0x7bd02384). It occurred during a read of page (1:0) in database ID 9 at offset 0000000000000000 in file 'G:\data\database.mdf'.

Steps I've tried:

  • create database for attach
  • dbcc checkdb(database, repair_allow_data_loss) - after creating empty db and copying MDF file over the new one)

Note - the recovery process identified several different versions of the same file with different extension, I already tried with each files, same error. There are no backups.

Anything else that could be tried?

3

A torn-page detection error on page (1:0) indicates the primary page of the primary database file is corrupted. This page contains a multitude of important details about the database. A sample output of DBCC PAGE for one of my databases shows:

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED           

File Header Data:

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 157                   
Memory Dump @0x000000000CFEA371

0000000000000000:   30000800 00000000 17000000 0016004b 004b004d  0..............K.K.M
0000000000000014:   004f0053 0057005b 005f005f 00690069 0069006d  .O.S.W.[._._.i.i.i.m
0000000000000028:   00710075 00790079 00790079 0083008d 009d004c  .q.u.y.y.y.y......L
000000000000003C:   3f4c23ac 53f24583 29615c2f 5ddaba01 00010000  ?L#¬SòE)a\/]Úº.....
0000000000000050:   19000000 32000080 0c000000 0000007b 000000d8  ....2..........{...Ø
0000000000000064:   0000000a 00000200 00000000 00ffffff ff001000  .............ÿÿÿÿ...
0000000000000078:   00000000 00000000 000000da 0000000c 0a000049  ...........Ú.......I
000000000000008C:   0015eb49 62ffe8ab 43be1bdc 9847daaa 7d        ..ëIbÿè«C¾.Ü.GÚª}
BindingID = 234c3f4c-53ac-45f2-8329-615c2f5ddaba                         FileIdProp = 1
FileGroupId = 1                     Size = 6400                         MaxSize = 12800
Growth = 3200                       Perf = 0                            BackupLsn = [NULL]
FirstUpdateLsn = (123:216:10)       OldestRestoredLsn = [NULL]          FirstNonloggedUpdateLsn = [NULL]
MinSize = 512                       Status = 0                          UserShrinkSize = 65535
SectorSize = 4096                   MaxLsn = [NULL]                     FirstLsn = [NULL]
CreateLsn = (0:0:0)                 DifferentialBaseLsn = (218:2572:73) 
DifferentialBaseGuid = 6249eb15-e8ff-43ab-be1b-dc9847daaa7d              FileOfflineLsn = [NULL]
FileIdGuid = [NULL]                 RestoreStatus = [NULL]              RestoreRedoStartLsn = [NULL]
RestoreSourceGuid = [NULL]          HardenedSkipLsn = [NULL]            ReplTxfTruncationLsn = [NULL]
TxfBackupLsn = [NULL]               FstrContainerSize = [NULL]          MaxLsnBranchId = [NULL]
SecondaryRedoStartLsn = [NULL]      SecondaryDifferentialBaseLsn = [NULL]
ReadOnlyLsn = [NULL]                ReadWriteLsn = [NULL]               RestoreDifferentialBaseLsn = [NULL]
RestoreDifferentialBaseGuid = [NULL]RestorePathOrigin = [NULL]          DatabaseEncryptionFileState = [NULL]
FCBFileDEK = [NULL]                 

If the first page is corrupted, that likely means you've not correctly fixed the deleted filesystem.

Clearly, having a well-tested recovery plan, even for development databases, might be considered A Good Thing™

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.