4

When you create a backup with Microsoft Sql Server, the resulting file is in Microsoft Tape Format. Different versions of SQL Server apparently use different version of this format.

Which version of SQL server is required to restore a given file with a specific format version?

Can I somehow influence the version SQL server uses when creating a backup?

Background: I am currently trying to restore a database backup created with an unknown version of SQL server, probably 2012. I have SQL Server Express 2008 R2. When executing the command

RESTORE VERIFYONLY FROM DISK='C:\myfile.bak'

I get the error

The media family on device 'C:\myfile.bak' was created 
using Microsoft Tape Format version 1.8. 
SQL Server supports version 1.0.

I guess this is simply a case of being unable to restore a database with an older version of SQL server, and there is nothing to be done about that besides upgrading. However I found no information about which MS tape format version corresponds to which sql server, and the error message is not very enlightening.

Therefore, in order to understand such error messages, I am interested in some general information about this format, in particular the exact correspondences between tape format version numbers to SQL Server version numbers.

Update:

It seems this is not simply a problem with the wrong version of SQL Server.

Restoring on SQL Server 2012 Express failed with the same error.

When executing a RESTORE HEADERONLY the resulting headers where

BackupName: *** INCOMPLETE ***
Position: 1
Every other column: NULL

I am currently in the process of acquiring another copy of the file.

Improve this question
6
  • The backup is corrupted. You would need a new backup inorder to restore it. – Kin Shah Jun 5 '14 at 13:44
  • 1
    @Kin That is a possibility I had not considered. I thought a corrupt file would result in some sort of failed checksum and a corresponding error message. Can a backup file randomly corrupt in such a way that it looks like a valid file with a different format version? – HugoRune Jun 5 '14 at 13:50
  • How did you get the backup file ? Through ftp ? Is it possible to restore on the same server that you took backup ? – Kin Shah Jun 5 '14 at 14:00
  • I got the file trough ftp and I am in the process of requesting another copy sent to me. Communication in this case is difficult however, and I am not confident in my ability to explain to them how to restore a backup without overwriting the production database. (Just as I am not confident that the sql server version reported to me is the correct one) – HugoRune Jun 5 '14 at 14:11
  • Have you tried running RESTORE HEADERONLY on the backup? If this succeeds, it will give you the server version where the backup was taken. If it doesn't, or it comes back with a weird result, then try the restore on a higher version. – Jon Seigel Jun 5 '14 at 14:17
2

The problem was indeed a corrupted backup, as Kin correctly deduced. Most likely the corruption was introduced during an ftp transfer.

So, when you get the error "The media family on device 'C:\myfile.bak' was created using Microsoft Tape Format version 1.8. SQL Server supports version 1.0." or the error "Specified cast is not valid. (SqlManagerUI)" when using the SQL Management studio Gui to restore a backup, you most likely have a corrupted backup file.

In my case, zipping the file before ftp transfer solved the problem.

You would think that backup files always contain a checksum, and if the file was later modified that sql server would report this incorrect checksum, but you would be wrong.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.