Restore DB — Error RESTORE HEADERONLY is terminating abnormally.

Question

I have taken backup of SQL Server 2008 DB on server, and download them to local environment.

I am trying to restore that database and it is keep on giving me following error.

---

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------ ADDITIONAL INFORMATION:

The media family on device 'C:\go4sharepoint_1384_8481.bak' is incorrectly formed. SQL Server cannot process this media family. RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4053&EvtSrc=MSSQLServer&EvtID=3241&LinkId=20476

I have tried to create a temp DB on server and tried to restore the same backup file and that works. I have also tried no. of times downloading file from server to local pc using different options on Filezila (Auto, Binary)

But its not working. After that I tried to execute following command on server.

BACKUP DATABASE go4sharepoint_1384_8481 
TO DISK=' C:\HostingSpaces\dbname_jun14_2010_new.bak' with FORMAT

It is giving me following error:

Msg 3201, Level 16, State 1, Line 1 Cannot open backup device 'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup\ C:\HostingSpaces\dbname_jun14_2010_new.bak'. Operating system error 123(The filename, directory name, or volume label syntax is incorrect.). Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally.

After researching I found the following 2 useful links:

1. http://support.microsoft.com/kb/290787
2. http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/4d5836f6-be65-47a1-ad5d-c81caaf1044f

But I am still not able to restore Database correctly.

Any help would be much appreciated. Thanks.

Answer 1

You can check out this blog post. It had solved my problem.

http://dotnetguts.blogspot.com/2010/06/restore-failed-for-server-restore.html

Select @@Version
It had given me following output Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) May 26 2009 14:24:20 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 6.0 (Build 6002: Service Pack 2)

You will need to re-install to a new named instance to ensure that you are using the new SQL Server version.

Answer 2

This type of error will come when you try to upload backup data from a higher version to lower version. Like you have backup of SQL server 2008 and you trying to upload data into SQL server 2005 then you will get this kind of error. Please try to upload in a higher version.

Answer 3

I ran into this issue and my problem was a bit more involved... Originally I was trying to restore a SQL Server 2000 backup to SQL Server 2012. Of course this didn't work cause SQL server 2012 only supports backups from 2005 and upwards .

So, I restored the database on a SQL Server 2008 machine. Once this was done - I copied the database over to restore on SQL Server 2012 - and it failed with the following error

The media family on device 'C:\XXXXXXXXXXX.bak' is incorrectly formed. SQL Server cannot process this media family. RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)

After a lot of research I found that I had skipped a step - I had to go back to the SQL Server 2008 machine and Right Click On the database(that I wanted to backup)> Properties > Options > Make sure compatibility level is set to SQL Server 2008. > Save

And then re-create the backup - After this I was able to restore to SQL Server 2012.

Answer 4

This error can be caused by the permissions to the file, which you should check, however recently I noticed that the same is thrown if the file has been transferred and windows has marked the file as 'Encrypt Contents to Secure Data'.

You can find this by bringing up the .bak file properties and clicking the advanced button, it appears as the last check box on the dialog.

Hope that helps someone!

Answer 5

I had a similar problem but I was trying to restore from lower to higher version (correct). The problem was however in insufficient rights. When I logged in with "Windows Authentication" I was able to restore the database.

Answer 6

My guess is that you are trying to restore in lower versions which wont work

Answer 7

I think you have 2 separate problems, 1. with restoring and 2. with creating

For 1. you could try checking to see if the file was transferred properly (one easy way would be to check the md5 of the file on the server and again on the local environment to see if they match).

Answer 8

Cannot open backup device 'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup\ C:\HostingSpaces\dbname_jun14_2010_new.bak'

The error is quite self-explanatory. The file C:\program files\...\Backup \c:\Hosting...\ is incorrectly formatted. This is quite obvious if you inspect the file name. Perhaps ommit the extra space in your backup statement?

BACKUP DATABASE go4sharepoint_1384_8481 
TO DISK='C:\HostingSpaces\dbname_jun14_2010_new.bak' with FORMAT

Note there is no space between ' and C:

Answer 9

In my case, the backup file was compressed, but the file extension didn't indicate this, didn't end in .zip, .tgz, etc. Once I decompressed my backup file I was able to import it.