How to restore server when Restore HEADERONLY is terminating abnormally?

Published by Jacob Martin on July 31, 2014

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 on: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4053&EvtSrc=MSSQLServer&EvtID=3241&LinkId=20476

I m sure you all might also come across such error while upgrading your SQL database. To solve these database problems there are few things that you should remember is that these error can occur due to many reasons but you must ensure the following before starting any solution. This will definitely save your lot of time.

1.    You need to ensure that the Backup copy of the Database is good :

•    Take a backup copy of your database and restore them on machine where you tried to make backup.

•    Then create a dummy of the database and then try to restore the database.

•   If you tried to restore the database successfully on the machine that created the backup than the backup copy is good.

You can also try alternate methods to try the backup of the database with the following command

BACKUP DATABASE NorthwindDB

TO DISK=’C:\HostingSpaces\MyBackupCopy_NorthwindDB.bak’ with FORMAT

If this will able to take the backup successfully then the Backup copy is good.

2.   Ensure that the backup copy doesn’t get corrupted while downloading:

Such as in my case I created a backup copy of Hosting Server, and stored it in the .Zip format and then tried to download using the FileZila with default settings of filezila transfer type, i.e. auto.

Till this point everything was going cool but still I was not able to restore back my DB files.

3.    Then most importantly open the SQL Query window and then check the version of your SQL server.

Then run the following command and then you see the output:

 Select @@Version

It will give you the following output as it gave me:

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 noticed that even though I was using SQL server 2008 express version it showed that I was using SQL Server 2005

Cause of this Error

This happen because even you are using the SQL Server 2008 the database is connected to SQL Server 2005 instances on the machine that I was trying to restore.

Remember: Restoration from the database from the lower version to higher version does not give any error. i.e. transfer from SQL Server 2005 to 2008 would not give any error. But restoring from the higher version to lower version always results to error.  As in this case the SQL server 2005 instance it results to above error.

So you need to validate the instance exhibits right version by “SELECT @@version”.

Solution

Then you need to fix the connection with the use of SQL 2008 instances.

For this try to run the SQL Server 2008 Express install program again during Name Instance configuration, and specify the Name Instance with the different name.  Example: MachineName\instancename

Then create the database, create tables for database and then try to run restore again. This will work.

You can even resolve this with the help of SQL Repair Tool. This powerful recovery tool effectively retrieves your new data and scans the damaged database file and repairs it to recover inaccessible objects in MDF and NDF database files. It repair and restores every items from the corrupt database including tables, keys, views, deleted records, stored procedures, triggers, indexes and so on.

Steps to restore server when Restore HEADERONLY is terminating abnormally

Step 1: Stop the running MS SQL server. Perform the repair task on the copy of the corrupt database, click on the ‘OK’ button to continue.

Step 2: Click on the ‘Select database’ button and select the path of corrupt MDF file. You can also search your corrupt database file by using ‘Look in’ and ‘File Type’ button. click on the ‘Scan file‘ button to start the repairing process.

Step 3: The recoverable objects of database are listed in a tree view on the left side of the window. You can see the preview by clicking on the object.

Step 4: You can also search for a particular object by using ‘Find item’ option. Write the object name or a part of the object name in the given text box, check on ‘Match case‘ or ‘Match whole word‘ and then click find next button.

Step 5: Click on the ‘Start Repair’ icon. A dialog box will appear fill the SQL server instance name. To save the repaired file in the desired location click on the brows button and give the path, else the repaired file will be saved in the ‘Default SQL Location’. Click on the ‘OK’ button.

1857 Total Views 3 Views Today