Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
Message
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?

Thanks!

share|improve this question
    
Open a call with Microsoft in the first instance. I've not been in this situation but I understand they will attempt data recovery from corrupted databases and backups. –  Mark Storey-Smith Mar 3 '12 at 11:46
6  
I don't know of anyone who is both willing and able to do these types of operations on a corrupt backup file. You would be better off focusing on the database. What is the status of the database? –  Robert L Davis Mar 3 '12 at 16:35
    
I actually don't know (haven't had any direct contact with the client). I get the impression they had a hard drive failure but I don't know what type of forensics/recovery they've tried or looked into. Thanks for the answers. –  James Mar 3 '12 at 17:06
4  
Having looked quite a bit at raw MDF files, I'll +1 Roberts advice. Having to choose between a corrupted BAK and a corrupt MDF, I'd much prefer looking into the MDF. If both are corrupt, the MDF will be less complicated as you can ignore the extra fluff caused by the MS Tape format in the BAK file. –  Mark S. Rasmussen Mar 3 '12 at 18:01
    
Thanks for the comments. I really appreciate it, especially considering who they're coming from. I wasn't able to get more information about what led to the client needing this BAK so badly, so I don't know if recovering the MDF is an option. It seems like I've done all I can reasonably do at this point. –  James Mar 6 '12 at 8:10
show 1 more comment

1 Answer

up vote 6 down vote accepted

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.

share|improve this answer
    
Hey thanks. This client ended up recovering the database from another source. –  James May 16 '12 at 15:08
add comment

protected by Mark Storey-Smith Feb 10 '13 at 0:41

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

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