Getting data from this MS SQL bak file

Question

The following commands

restore filelistonly from disk = 'C:\....bak'
restore verifyonly from disk = 'C:\....bak'

produce this error

The media family on device 'C:\...bak' is incorrectly formed. SQL Server cannot process this media family.

This command

restore headeronly from disk = 'C:\....bak'

produces

BackupName  BackupDescription   BackupType  ExpirationDate  Compressed  Position    DeviceType  UserName    ServerName  DatabaseName    DatabaseVersion DatabaseCreationDate    BackupSize  FirstLSN    LastLSN CheckpointLSN   DatabaseBackupLSN   BackupStartDate BackupFinishDate    SortOrder   CodePage    UnicodeLocaleId UnicodeComparisonStyle  CompatibilityLevel  SoftwareVendorId    SoftwareVersionMajor    SoftwareVersionMinor    SoftwareVersionBuild    MachineName Flags   BindingID   RecoveryForkID  Collation   FamilyGUID  HasBulkLoggedData   IsSnapshot  IsReadOnly  IsSingleUser    HasBackupChecksums  IsDamaged   BeginsLogChain  HasIncompleteMetaData   IsForceOffline  IsCopyOnly  FirstRecoveryForkID ForkPointLSN    RecoveryModel   DifferentialBaseLSN DifferentialBaseGUID    BackupTypeDescription   BackupSetGUID   CompressedBackupSize
*** INCOMPLETE ***  NULL    NULL    NULL    NULL    1   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL

I verified the .bak file did indeed come from a Microsoft SQL Server. It is not encrypted. It is doubtful that it was created from Microsoft SQL Server 2012 because the old system was several years old. The import was attempted using a temporary install onto a Vista machine with SQL Server 2008 R2.

The goal is to get the data into either a different database (for example, using ODBC), or into a plain data type (.csv, .sql, .xml, etc) that we can create an import program for.

Do you suggest a different method other than TSQL for performing this import?

Do you have an alternate explanation for the error message? I am not inclined to guess whether I should try again on a newer or older SQL Server Version because every re-install takes a significant amount time, which is a limited resource.

score 3 · Accepted Answer · answered Jul 25 '12 at 21:30

3

If this is not a version mismatch, then the backup file could be actually corrupted.

If this is the case, you're not going to be able to (at least easily) recover anything from it.

answered Jul 25 '12 at 21:30

Massimo

68,714
56
196
319

If I use the most recent SQL Server version, would it cover all previous versions? – 700 Software Jul 25 '12 at 21:34
Yes. See http://msdn.microsoft.com/en-us/library/ms186858.aspx. – Massimo Jul 25 '12 at 21:55
1

Turned out to be a 2012 on the original system. – 700 Software Jul 26 '12 at 12:42

score 2 · Answer 2 · answered Jul 25 '12 at 21:41

2

Are you sure that the backup consisted of one file?

If you have the free space on the source server that the backup came from (assuming it is still operational) you could try restoring the backup to a new database to confirm that the backup is good

answered Jul 25 '12 at 21:41

Chris McKeown

7,128
1
17
25

I am not directly able to access the server it came from. – 700 Software Jul 25 '12 at 21:41
Does this also mean that you might not have been given the full set of .BAK files that make up the backup set? – Chris McKeown Jul 25 '12 at 21:44
It is possible, but I expect that if there is more than one bak file, they would have sent them all. I will attempt to determine, just wanted to get input here, in case I am missing something. So far it just appears to be either a version mismatch, or actually a corrupt file, or lack of files. I don't yet know if I should try using an older or newer version and attempt the import again. – 700 Software Jul 25 '12 at 21:49

score 0 · Answer 3 · answered Jul 26 '12 at 03:42

0

Either the file's header is corrupt or this is one of the files from a backup set where the backup to spread over multiple files.

You'll need to get a new backup of the database, or you'll need to get the other files from the backup set.

answered Jul 26 '12 at 03:42

mrdenny

27,074
4
40
68

Getting data from this MS SQL bak file

3 Answers3