Recently I have upgraded my database from SQL Server 2005 Express to SQL Server 2008 Express, and I have notice on restoring database it started giving me following restore error.
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The media family on device 'C:\NorthwindDB.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
Few things you should try before going for solution
This error can occurs due to many reasons but you must ensure following before you start for any solution, it would save your lot of time.
1) Ensure Backup copy of Database is good.
- Take a backup and store on machine where you tried to take backup.
- Now create one dummy database and try to restore that database.
- If you are able to restore that database successfully on machine backup was created, than your backup copy is good.
You should alternately try taking backup using following command
BACKUP DATABASE NorthwindDB
TO DISK='C:\HostingSpaces\MyBackupCopy_NorthwindDB.bak' with FORMAT
If you are able to take backup successfully than Backup copy is good.
2) Ensure Backup copy doesn't get corrupted during dowload.
In my case, I have created a backup copy on Hosting Server, than stored it in .Zip Format, and than try to download it using FileZila, with default settings of filezila transfer type, i.e. Auto.
Till this point everything was going good, but still I was not able to restore DB.
3) Important : Open SQL Query window and check version of your SQL Server.
Run following command and see the output.
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)
After running this command i notice that even though i am using SQL Server 2008 express, it is showing that i am using SQL Server 2005
Cause of Error
Even though I am using SQL Server 2008, but it was connected to SQL Server 2005 instance on the machine i was trying to restore.
Remember: Restoring Database from lower version to higher version would not give error. i.e. SQL Server 2005 to 2008 would not give error. But restoring from higher version to lower version will always result in error. In my case as i was connected to SQL Server 2005 instance it results me in above error.
You should validate that the instance is the right version by "SELECT @@version".
You need to fix the connection so it is using the SQL 2008 instance.
Try to run the SQL Server 2008 Express install program again, and during Name Instance configuration, specify a Name Instance with a different name. Example: MachineName\instancename
After installation, logged in with New Instance name created.
Try to create database, create tables for database and try to run restore again. It will work this time.
More about Instances
An instance of a SQL Server database is just a named installation of SQL Server. The first one you install on any given machine typically is the default instance, which has no specific name - you just connect to it using the (local) name or the name of the server machine.
SQL Server Express typically installs as a SQLExpress instance - so the instance is called SQLExpress, and you connect to it using (local)\SQLExpress or .\SQLExpress or MyServerMachine\SQLExpress.
SQL Server instances are totally independent of one another, and you can basically install as many as you like - you just have to make sure to use unique instance names for each. But when you run the SQL Server Installation again, it will show you what instances are already installed, and prompt you for a new instance name for a new installation.