Error 3241: The Media Family on Device Is Incorrectly Formed

Fix it now

The file you pointed a restore at is not something this instance can read as a complete backup set, and the restore never reached the database inside. Three situations cover nearly all of them: the file is truncated or still being copied, it was written by third-party software, or it is one member of a striped set.

RESTORE HEADERONLY FROM DISK = N'D:\bk\MyDb.bak';
RESTORE LABELONLY  FROM DISK = N'D:\bk\MyDb.bak';

1. If HEADERONLY also fails, stop restoring. The file is not a native backup set this instance can read, and no restore option changes that.
2. Read the family count from LABELONLY and compare it with how many files you actually hold. A count above one means a striped set.
3. If it was striped, name every member in one statement: RESTORE DATABASE [MyDb] FROM DISK = N'D:\bk\p1.bak', DISK = N'D:\bk\p2.bak' WITH RECOVERY;
4. Compare the file size against the source, and verify the copy: certutil -hashfile D:\bk\MyDb.bak SHA256
5. If the file came from third-party backup software, restore it with that software. Native RESTORE cannot open a proprietary container.

3132 is the most useful member of this family because it counts for you: it prints how many media families the set has and how many you supplied.

If HEADERONLY lists the backup sets you expected, the file is fine and you can restore. If not, the next section explains how these files get broken.

Why it happens

A native backup writes to a media set. The media set is made of one or more media families, which in practice means one or more files, and each family holds part of the stream. Inside the set are backup sets, one for each backup operation, which is why several backups can live in one file. RESTORE has to read header structures at both levels before it can do anything useful, and this family of errors is what you get when one of those structures does not parse.

The four codes divide the problem cleanly. 3241 says the media family on the device is incorrectly formed and SQL Server cannot process it. 3242 is blunter: the file on the device is not a valid Microsoft Tape Format backup set at all, which is what you see with a file produced by a different backup product, or with a file that is not a backup. 3243 says the media family was created using a Microsoft Tape Format version this instance does not support, and it prints both numbers. 3132 counts the media families and tells you how many you supplied.

Truncated files are the most common cause and the easiest to miss, because a partial file looks entirely plausible in Explorer. A copy interrupted by a network drop, a file transferred in text mode rather than binary, a file read off a share while the backup was still writing to it, or a download that stopped short all produce something with the right name and the wrong contents. There is no partial restore: a backup set is meaningful only complete.

Full reference

Working out which of the five you have

The three statements that describe a file without restoring it

If HEADERONLY succeeds, the file is structurally sound and any subsequent failure is about the database inside it rather than about the media. That single fact saves a lot of time, because it moves the investigation from the file to the restore statement.

Why a striped set cannot be partly restored

The stripes are written in parallel and each one holds a fraction of the stream, interleaved. There is no sense in which the first file contains the first half of the database. That is the main argument against striping to targets you cannot guarantee to keep together, and it is why 3132 exists as its own error: the engine can tell you exactly how many members it is short.

Preventing the silent version of this

• Back up WITH CHECKSUM so the backup itself detects damaged pages as it writes them.
• Run RESTORE VERIFYONLY WITH CHECKSUM at both ends of any transfer, as a scheduled step rather than a habit.
• Hash the file after transfer with certutil -hashfile <path> SHA256 and compare. Microsoft documents certutil’s hashfile option with MD5, SHA1, SHA256, SHA384 and SHA512.
• Never read a backup file while the job that writes it is still running.
• Copy with a tool that reports failure. A drag-and-drop copy that silently stops is how most truncated backups are made.

What no tool can do

Products exist that claim to repair a broken .bak file, and it is worth being clear about what is possible. If the file is truncated, the missing bytes are not anywhere in it and nothing invents them. If the format is proprietary, the answer is the product that wrote it. The effort is better spent getting a good copy from the source, or restoring the previous full backup and rolling forward, than on a tool that promises to reconstruct what was never transferred.

Every code this article covers

Confirm the fix worked

1. Run RESTORE HEADERONLY FROM DISK = N'<path>'; and confirm it lists the backup sets in the file.
2. Run RESTORE VERIFYONLY FROM DISK = N'<path>' WITH CHECKSUM; and confirm it reports the set as valid.
3. Complete the restore and confirm the database comes online: SELECT name, state_desc FROM sys.databases;
4. Run DBCC CHECKDB (N'MyDb') WITH NO_INFOMSGS; on the restored copy before putting it into service.
5. If the file was transferred, compare hashes at both ends so you know the next transfer is trustworthy.

Questions people ask about this