SQL Server Fix : - The media family on device xxxxx.bak is incorrectly formed. SQL Server cannot process this media family. Msg 3241, Level 16, State 7, Line 1

I received an error message during a SQL Restore of a .BAK file.

Msg 3241, Level 16, State 7, Line 1 The media family on device 'xxxx.bak' is incorrectly formed. SQL Server cannot process this media family.

Some of the advice found on various blogs and forums is

1. If backup is correct and one is trying to restore on lower version
2. The file changed from binary mode to text mode during a file transfer
3. In the RESTORE statement FILE=2 was specified – usually used for TRN and not FILE=1 used for BAK files

None of these suggestions were related to fixing the problem.

Diagnosis

1. Transfering the BAK file from the source server to another server and restoring was successful
2. Transfering the BAK file from the source server onto another drive and restoring was successful
3. The process started failing on the same day some corruption was noticed on the C:\. The server administrator fixed the C:\ issue. I couldn’t see a direct connection , but this was clearly suspicious .
4. As a first step I refreshed dlls relevant to the copy and restore process, just in case of any another corruption. This didn’t solve the problem.
5. I checked with storage guys who confirmed a physical drive failed on the same day the Msg 3241, Level 16, State 7, Line 1 started. The problem could be traced through the various layers of V7000, SVC and VM Datastore, to the xx: drive. Although the OS reported C: drive corruption on this date, The restore job involving xx.BAK, failed every day thereafter.
6. I requested the xx drive to be replaced from a different data store. I reran all the processes and they were successful.

  Conclusion

       In a multi-layered infrastructure including VM, storage layer and SVC , multiple experts may be required. Also, monitoring of component failures needs to be understood  as part of wider impact.In this case, some type of storage corruption led to a SQL Server job failure.

I find it helps to Write everything down during problem troubleshooting  during troubleshooting.

Read More on corruption

Find database corruption with msdb.dbo.suspect_pages - SQL ...

SQL Performance tuning - Drilling into the problem - SQL Server DBA