Sunday, June 27, 2010

Restore failed for Server - RESTORE HEADERONLY is terminating abnormally

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.

Error Details

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


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


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.

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)

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".


Solution
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.

5 comments:

SelArom said...

thanks for this, we were pulling our hair out until we did the SELECT @@version and discovered that even though we were running the Management software for 2008 R2, the instance was 2005!

thanks!

Prof. Roberto - Matemática Noturno - Gabriel Ortiz said...

Obrigado!!! Era a versão do SQL que estava utilizando.

Thanks!!! Was the version of SQL that I was using.

daksh bhatt said...

thank you it helps me in my project . . .

Brijesh Damai said...

Thanks gentleman, solution worked after installing SQL 2008 with different instant Name > created new data and restored to it.

Post that I have to route my application database there....

Thanks, Brijesh Damai

chandu said...

Thanks...

Most Recent Post

Community Updates

Subscribe Blog via Email

Enter your email address:



Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog.
Page copy protected against web site content infringement by Copyscape