SQL Server 2012 – How to Recover Database with MDF File Only

sql serversql-server-2012

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?

Best Answer

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™

Related Question