Problem with local Restore of Database backup

Summary:

I am facing a quite unpleasant problem with the Local restore of SQL 2008 Backup files. Downloading the file created by the Control Panels MS SQL 2008 => SQL Tool Suit (Backupfilename.bak) it seems impossible to restore them with the normal “Restore database process” of MS SQL Server 2008 Management Studio Express.
Whenever trying to use the downloaded Backup file an Error message is displayed stating:

“An exception occurred while executing a Transact-SQL statement or batch (Microsoft.SQLServer.ConnectionInfo)
Additional Information: The media family on device ‘C:\Backup\Backupfilename.bak’ is incorrectly formed. SQL Server cannot process this media family. RESTORE HEADERONLY is terminating abnormally (Microsoft SQL Server, Error: 3241)”

Details of Restore process used:
After creating the normal Database Backup files in “Zip” as well as “Bak” format, I downloaded both with FileZilla with transfertype set to “binary” and tried to restore them in SQL Server Management Studio.
For Restoring I used the standard process: On the context menu of the database to restore I select: Tasks => Restore => Database to get the restore dialog. Database is specified in “To database” and the Backupfilename.bak is selected in the source and location area via the option “From Device”.
When confirming the Backupfilename.bak in the “Sepcify Backup dialog” of the “From Device browse command” the Errormessage is shown.

Searching extensively on the internet only 2 reasons are stated for this Errormessage:
a) Oversize of the backup (which is not the case as my file has approximately 10MB)
b) Backup-file is corrupted

Ruling out the oversize option, I tried the following to ensure that the Backupfile is not corrupted.

I created a backupfile in the Control panel.
I downloaded the file.
I Deleted the backupfile from the _Database location on the DiscountASP FTP location
I Deleted in my Webapplication data from the database located on DiscountASP.
I used the downloaded file to try a local restore (=> Error message)
I uploaded the same file to the DiscountASP FTP location _Database
I restored the Database via the Control panels SQL 2008 Tool suite “with success!”
I reconfirmed the existence of the Data which were deleted for test reasons (see above)

From this I reason that the file is NOT corrupted.

I don’t know what I do wrong here, but I definitely have reached the point where I don’t know what to try next.

Any hint or ideas would be very much appreciated.

============================================
Details of Software used:
============================================

I am using the following component environment of SQL Server 2008 Express.
Microsoft SQL Server Management Studio: 10.0.1600.22 (SQL_PreRelease).080709-1414 )
Microsoft Data Access Components (MDAC): 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML: 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer: 6.0.2900.2180
Microsoft .NET Framework: 2.0.50727.3053
Operating System: 5.1.2600

For File Transfer I am using FileZilla Client Version: 3.2.4
Build information:
Compiled for: i586-pc-mingw32msvc
Compiled on: i586-pc-mingw32msvc
Build date: 2009-04-21
Compiled with: i586-mingw32msvc-gcc (GCC) 4.2.1-sjlj (mingw32-2)
Compiler flags: -g -O2 -Wall -g -fexceptions
============================================

With best regards,
Michael

 

File can't be corrupted

Dear Sir,

I ran the suggested command with the same Error result.

Nevertheless I very much doubt that the file is corrupt, as I took the "same" file I downloded (which you suggest is currupt) and uploaded it back to your server. Restoring the Database with this file on your server is perfectly working.

Hence I don't think that the file corrupts on download and miraculously repairs itself on upload.

What kind of Backupsettings are used by DiscountASP when creating the backupfile?

I use SQL Server 2008 Express with the exact details mentioned in my first post. Do I have to use another version of SQL Server?

I really would like to find a way to solve this.

With best regards,

Michael

 

Try running the Restore Verifyonly command on your .bak file. This will check if the SQL backup file is readible.

 

Problem solved

I tried analyzing the problem in a different way. I made clean Windows install on a VirtualPC and installed SQL Server 2008 Express. With this install the backupfile perfectly works.

Comparing the installed elements between my "Non-working" operative Setup and the "working" virtual clean-install I noticed a number of SQL 2005 express components on my productive environment which surprised me as I never installed SQL 2005 express.

After having done further reading on the net it became clear that these components come from VS2005 which I have installed parallel to VS2008.

Removing all SQL 2005 related components and doing a new SQL 2008 Express setup solved the problem.

Conclusion: It is essential for anyone installing SQL 2008 express on a system where Visual Studio 2005 is installed to remove the SQL 2005 Express components which are installed by VS2005.
For a workable order of installs the following link might be of help.
http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/44e11565-546c-4ca1-9b44-43b15c34c2ef

This might maybe also be a topic for the knowledge base.

The topic is herewith settled.
Thank you for your input.

With best regards,

Michael