asked
2008R2 Restore *.bak Header failure
We routinely test dev code from backup files. Both supplier & test machines are SQLExpress 2008R2. I intermittently get Error: "The media family on device (file.bak) is incorrectly formed. SQL Server cannot process this media family. RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241). Side X Side comparison of 2008R2 are same. Is there a configuration, patch, setting to fix this? Additional Info: Backup was 1 month ago and also backup was last night. My SqlExpress ver 9.0.4035SP3 Two different suppliers with same problem. Backup was 'Local' file group. Usually we backup & massage for local testing offline. (remove proprietary information, emails, replication, etc). Restore, massage (client info), apply any updates (schemas), test, delete. Testing, ususally for specific reproducible problems. Local client data is deleted when testing completed. Typically in SMO is simple backup File, Task, Backup (full).
File size is 184,283 k, mwDb.bak This happens sometimes but frequently enough to ask the question. When pressing the help button, Microsoft response: We're sorry. There is no additional information about this issue in the Error and Event Log Message or Knowledge Base databases at this time. You can use the links in the Support area to determine whether any additional information might be available elsewhere.
So you do the restore, and then "massage" the restored data?
Does it fail every time, or only sometimes? This error does indicate that your backup files are corrupt. Or, occasionally, you're getting files from a SQL Denali installation to restore. (Not sure what happens what error you get if you restore from a backup taken more than a couple of versions old, but it could be the same...) EDIT: When you get one of these "corrupt" backups, do you get the supplier to try to restore their original of the backup? Something else to consider - how big are the backups, and how are they being transferred to you? If corruption is the issue, and shifting a smaller file is less prone to these issues, then try getting a copy of Quest's LiteSpeed for SQL Server, or Redgate's SQL Backup, or Idera's SQL Safe, or... (other tools are available).
Lenny, are you backing up to local disk, or a network share? I tend to agree with Thomas here, the backup files seem to be getting corrupted at some point in the backup/restore process. It could be worthwhile doing a couple of things: - Try local vs. Netowrk Backup - Try running DBCC CHECKDB (should be part of routine maintenance) and check the output for any information - Try looking at disk health (RAID Controller or SMART information). How old are the disks? Are they out of warranty? - Also look at your patch levels (OS, SQL Server, Hardware, BIOS)
Would have suggested db size is issue if restoring onto SQL Express but you say source is also Express edn? *edit* You state the file size as well below 10GB so, in that case you must consider its a corruption of the backup file. Does the backup get created on local disk? I would check network activity at time of backup if its copied over LAN, may be a faulty copy or it might be faulty partitions on your HDD. Have you got any HDD maintenance tools or done a checkdisk recently?
