1

I've got a server in the UK with SQL Server 2008. It's very locked down firewall-wise.

  • We want a local duplicate of the DB to test and build a new report.

  • Backing up the relevant DB, and then transferring over FTP (binary-mode) to here in the USA ends up saying in SQL Server 2012:

    Media family on device is incorrectly formed. SQL server cannot process this media family.

  • I also tried transferring using File Transfer 1.2j (from Sourceforge), as well as splitting them into zips/7zip chunks and copying them slowly through RDP.

  • None of them load on our SQL Server 2012 machines. This is the key difference between my case and other SO posts I've read. They're going backwards in version. I'm going forwards, which is supposed to be supported.

  • I've also verified the files on both ends using certutil to generate SHA-1 hashes.

  • Lastly, the backup CAN be restored in the UK into itself! So the backup isn't corrupt when coming from SQL Server. I'm also 99% sure it's a copy-only backup so it doesn't need any other differential files.

So the only thing left I see is corruption across the internet (yet SHA-1 says otherwise), or, we're not saving correct backups, or, somehow our USA SQL doesn't want to load the older UK SQL. Does localization even matter, especially if they're both English?

Improve this question
3
  • Have you tried restoring to a SQL 2012 server in the UK which has same specs as the US servers? (To isolate away from transport as a possible cause)
    – pelazem
    Mar 12, 2016 at 20:47
  • Was the source backup made with a third-party tool (like Litespeed)?
    – Andrew Loree
    Mar 13, 2016 at 6:15
  • I personally made the backup in SQL Management studio. Is LiteSpeed integrated in, or external? I've also dumped a smaller sub-section of the DB tables and restored them into a new DB name on the same server successfully. I did export a copy-only backup by selecting that option. Are there other settings I could have missed that allow it to restore on UK (because all the files are there) but not on a new server?
    – Katastic Voyage
    Mar 14, 2016 at 19:25

1 Answer 1

Reset to default
1

As Andrew Loree suggested, this might be due to a 3rd party SQL backup product being installed on the source instance.

In order to find out what software was used to take the backup, you can run a RESTORE HEADERONLY on the .bak file from your SQL2012 instance. That should give you some information about how the backup was created.

RESTORE HEADERONLY FROM DISK='X:\file.bak'

If the above also fails, then there would be corruption, but you've already ruled out corruption via SHA1 checksums.

Improve this answer
3
  • I still get Msg 3241, Level 16, State 0, Line 1 is incorrectly formed. SQL Server cannot process this media family. But it works on the UK server. Is there any field I should be looking at from that query on the working server? BackupType = 1 Compressed = 0 Position = 1 DeviceType = 2 DatabaseVersion = 655 UnicoldeLocaleId = 1033 CompatibilityLevel = 100 SoftwareVendorId = 4608 Is it possible that it's a user issue since I don't have the same users on the USA server yet? If I recall correctly, it should import and just not let me access it.
    – Katastic Voyage
    Mar 14, 2016 at 16:18
  • SoftwareVendorId=4608 means that it is a normal SQL Server backup. So it was not taken with a 3rd party backup software. I read that apparently this error message can also happen when you are trying to restore to a SQL Server Express Edition instance (which has a maximum database size limit)
    – Christoph Wegener
    Mar 14, 2016 at 21:57
  • The output of RESTORE HEADERONLY should also tell you if the backup set contains checksums (HasBackupChecksums = 1). Backup checksums can then be used to run a RESTORE VERIFYONLY
    – Christoph Wegener
    Mar 14, 2016 at 22:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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