Error 3241 on Restore: Newer Version or Damaged Backup File

Error 3241 on a restore means SQL Server could not read the file as a backup at all. It does not say why. A newer version is only one theory, and a damaged or wrong file is another.

The restore that fails late at night

A colleague sends you a backup. You copy it, start the restore, and get error 3241 about an incorrectly formed media family. Your first thought is “it must be from a newer SQL Server.” Maybe. But I have seen the same message from files that were not backups at all.

So let me do what I do on a real server: break a backup in a few different ways and read what SQL Server says each time.

This demo creates a small database called SqlAuthorityDemo and a backup file in C:\Temp. Create that folder first if you do not have it. The SQL Server service account must be able to read and write there.

Start with a backup that works

First make a good backup, then ask SQL Server to verify it. VERIFYONLY reads the backup without restoring anything. A healthy file says the backup set is valid.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
GO
CREATE DATABASE SqlAuthorityDemo;
GO
BACKUP DATABASE SqlAuthorityDemo
TO DISK = N'C:\Temp\SqlAuthorityDemo.bak'
WITH INIT, FORMAT;

RESTORE VERIFYONLY FROM DISK = N'C:\Temp\SqlAuthorityDemo.bak';

The last message reads: The backup set on file 1 is valid. Now we have something to compare against.

Three kinds of broken file

Now create three bad files. Run these PowerShell commands, one per line. The first is a text file with a .bak name, about 8 KB. The second is a very small text file. The third is a real backup cut off after 64 KB, like a copy that stopped halfway.

Set-Content -Path C:\Temp\NotABackup.bak -Value ('This is plain text, not a backup. ' * 250)

Set-Content -Path C:\Temp\Tiny.bak -Value 'not a backup'

$b = [IO.File]::ReadAllBytes('C:\Temp\SqlAuthorityDemo.bak'); [IO.File]::WriteAllBytes('C:\Temp\Truncated.bak', $b[0..65535])

Verify the text file first. This one fails on purpose, so expect red messages.

RESTORE VERIFYONLY FROM DISK = N'C:\Temp\NotABackup.bak';

There it is: error 3241, the media family is incorrectly formed. SQL Server then adds error 3013, which only says the verify is terminating abnormally. The real news is 3241. Now try the other two files.

RESTORE VERIFYONLY FROM DISK = N'C:\Temp\Tiny.bak';
GO
RESTORE VERIFYONLY FROM DISK = N'C:\Temp\Truncated.bak';

The tiny file gives error 3254, which says the volume on the device is empty. The truncated backup gives error 3203, a read that failed because it reached the end of the file. Three different files, three different messages. Only the first one was 3241.

That matters. A half-copied backup is a classic cause, and here it did not look like 3241. So 3241 points at the start of the file, not at a copy that stopped early.

Look at the first bytes

You can see this yourself. This query reads each file as raw bytes, then shows the first four characters and the file size. It needs permission to bulk read files, so use a test server.

SELECT N'SqlAuthorityDemo.bak' AS FileName,
       CAST(SUBSTRING(BulkColumn, 1, 4) AS varchar(4)) AS FirstBytes,
       DATALENGTH(BulkColumn) AS Bytes
FROM OPENROWSET(BULK N'C:\Temp\SqlAuthorityDemo.bak', SINGLE_BLOB) AS f
UNION ALL
SELECT N'NotABackup.bak', CAST(SUBSTRING(BulkColumn, 1, 4) AS varchar(4)), DATALENGTH(BulkColumn)
FROM OPENROWSET(BULK N'C:\Temp\NotABackup.bak', SINGLE_BLOB) AS f
UNION ALL
SELECT N'Tiny.bak', CAST(SUBSTRING(BulkColumn, 1, 4) AS varchar(4)), DATALENGTH(BulkColumn)
FROM OPENROWSET(BULK N'C:\Temp\Tiny.bak', SINGLE_BLOB) AS f
UNION ALL
SELECT N'Truncated.bak', CAST(SUBSTRING(BulkColumn, 1, 4) AS varchar(4)), DATALENGTH(BulkColumn)
FROM OPENROWSET(BULK N'C:\Temp\Truncated.bak', SINGLE_BLOB) AS f;

The good backup and the truncated one both start with TAPE. The two text files start with their own words. The truncated file is exactly 65536 bytes, the length we cut it to. A real backup starts with those four letters, so a file that does not has never been a backup of this kind.

What about a newer version?

I cannot show you a newer-version backup here, so I will not guess what its message looks like. What I can show is where to look. RESTORE HEADERONLY on a good backup lists the version that wrote it. The columns are SoftwareVersionMajor, SoftwareVersionMinor and SoftwareVersionBuild. Compare them with your destination server.

RESTORE HEADERONLY FROM DISK = N'C:\Temp\SqlAuthorityDemo.bak';

SELECT SERVERPROPERTY('ProductVersion') AS DestinationVersion;

If HEADERONLY itself fails, you never get that far. Then check the size against the source, compare a file hash, and copy the file again before you blame the version. Finish with the SQL cleanup, then delete the test files from PowerShell.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;

Remove-Item C:\Temp\SqlAuthorityDemo.bak, C:\Temp\NotABackup.bak, C:\Temp\Tiny.bak, C:\Temp\Truncated.bak

Next time a restore fails, read the first bytes of the file before you blame the version.

Error 3241 is not a version verdict, it is an unreadable file.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.