Fix SQL Server error 3241: media family is incorrectly formed

Error 3241 appears when SQL Server cannot understand the backup file it is asked to read. There are four usual causes: the backup came from a newer SQL Server version, the file was damaged in transfer, part of a multi-file backup is missing, or the file is not a SQL Server backup at all.

The error

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

Cause 1: the backup is from a newer SQL Server version

SQL Server can restore backups from the same or older versions only. A backup made on SQL Server 2022 will not restore on 2019. Compare the two servers:

SELECT SERVERPROPERTY('ProductVersion') AS version,
       SERVERPROPERTY('Edition')        AS edition;

Fix: restore on a server with the same or newer version. There is no way to restore a newer backup on an older server. To move data to an older version, script the schema and data out (or use export tools), rather than using backup and restore.

Cause 2: the file was damaged or cut short

Common culprits are an FTP transfer in ASCII mode, a download that stopped early, or a copy from a failing disk. Compare the file with the original using a hash:

Get-FileHash 'D:\Restore\Sales.bak' -Algorithm SHA256

Run the same command on the source copy. If the hashes differ, copy the file again in binary mode. If the original is also damaged, try an older backup.

Cause 3: a striped backup with missing parts

A backup can be written to several files at once (TO DISK = 'a.bak', DISK = 'b.bak'). Restoring needs every part. Check the FamilyCount column:

RESTORE LABELONLY FROM DISK = N'D:\Restore\Sales_1.bak';

If FamilyCount is greater than 1, list all the files in the RESTORE statement.

Cause 4: not a native SQL Server backup

Some backup tools write their own format with a .bak extension, or compress or encrypt the native file. A zip renamed to .bak produces the same error. Extract or convert it with the tool that created it first.

Prevent it next time

• Back up with CHECKSUM and run RESTORE VERIFYONLY on the destination copy, not only the local one.
• Keep more than one generation of backups, so one bad file is not your only copy.
• Test restores regularly, as described in how to verify a SQL Server backup.