I'm looking at a problem for someone who was backing up to a cloud-based service. Apparently the customer's server crashed while it was performing some maintenance on the cloud-based service's datastore, and this left the datastore in a corrupted state. At the same time, the customer's server was unrecoverable due to the crash.

The cloud-based service's developers had to write a script to find chunks of data that belonged to a database backup and stitch them together into a file.

This resulted in a backup (.bak) file that I can tell has data in it (I can run 'strings' on it and see a header, along with file names, table names, rows of data, etc), but won't be processed by SQL Server. I get:

restore headeronly from disk = 'c:\restore.bak'

Msg 3241, Level 16, State 0, Line 1
The media family on device 'c:\restore.bak' is incorrectly formed. SQL Server cannot   
process this media family.
Msg 3013, Level 16, State 1, Line 1
RESTORE HEADERONLY is terminating abnormally.

Both the source instance on the machine that died and the one I'm testing the restore on are SQL Server 2008 R2, so unfortunately version mismatches aren't the source of the error.

I compared with a valid backup file of another database in a hex editor (xvi32) and noticed that the valid backup file began with the string 'tape' at byte 0, but the corrupt one had some other data with no discernible meaning for 158 bytes before that. I deleted 158 bytes from the corrupt backup up to the string 'tape' and that got me a little farther. Now 'RESTORE FILELISTONLY' worked, whereas before it threw the same error as above.

'RESTORE HEADERONLY' now displays 'incomplete' instead of throwing error 3013. If I trace it in Process Monitor, I can see it tries to read 512 bytes at offset 7168, then tries to read another 512 bytes at an offset past the end of the file. The file is almost certainly incomplete; it was originally 95.8 MB according to a reporting tool in the cloud-based service, but is only 92.95 MB on my filesystem.

I tried forcing a restore on the partially repaired (?) file:

restore database corrupt from disk = 'c:\restore_partiallyrepaired.bak' with continue_after_error, move 'corrupted' to 'c:\corrupt.mdf', move 'corrupted_log' to 'c:\corrupt.ldf'

This made SQL Server crash but gave a clue:

Date        3/3/2012 9:52:06 AM
Log     SQL Server (Archive #1 - 3/3/2012 9:52:00 AM)
Source      spid55
The backup data at the end of "c:\restore_partiallyrepaired.bak" is incorrectly formatted.    
Backup sets on the media might be damaged and unusable. To determine the backup sets on 
the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run 
RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using 
BACKUP WITH FORMAT, which destroys all the backup sets.

 RESTORE detected an error on page (48:538976288) in database "corrupt" as read from the   
 backup set.

 Hit Fatal Error: Server is terminating

I tried various methods for 'hack-attaching' the 42.5 MB MDF that the restore produced, but it was unrepairable using DBCC CHECKDB.

At this point I'm going to send this back to the cloud-based service's support team with my findings and see if this gives any clues to them about how to extract a non-corrupt file.

I guess my questions are:

1) Is there anything else I can try here?

2) Is there anyone you know of who could help on a billable basis? For example, could MS support or some ex-MS consultants hack this backup file into a usable format?


closed as off-topic by Paul White Feb 23 at 1:10

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Tip of the iceberg - the question or comments reveal an underlying issue that would need extensive investigation by a consultant or database vendor support team: issues like this do not fit the SE Q&A model well. For more information see this meta post." – Paul White
If this question can be reworded to fit the rules in the help center, please edit the question.

  • The customer ended up recovering the DB from another source. – James L Mar 13 '12 at 20:11

I do know a consultant who does this kind of work, but it's nowhere near cheap - think mid-five-figures to start the project, paid in advance. It's not for the faint of heart and you only want to go that route as a last resort.

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