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!