Failed to restore a SQL2000 backup to a SQL2008R2 instance

Question

Failed to restore a SQL2000 backup to a SQL2008R2 instance

sqlnickxu

SSCertifiable

Points: 5026
More actions
October 20, 2011 at 1:13 am

#259015

Hello gents
The SQL2000 backup is fresh without corruption but when I tried to restore it to a SQL2008R2 instance, the following error message pops up:
System.Data.SqlClient.SqlError: The media family on device 'C:\temp\<database_name>.bak' is incorrectly formed. SQL Server cannot process this media family.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)
I hope someone else already had this issue before and maybe a solution too?
BTW, the SQL2008R2 instance is 64-bit enterprise edition. Can there be a possible conflict between 32-bit backup and a 64-bit machine?
Bazinga!

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

sqlnickxu SSCertifiable Points: 5026 More actions · Answer 1

I've tried to run "restore verifyonly from disk = <backup_file_location>" on both 2000 and 2008R2 instances and got the following messages:

on SQL2000

The backup set is valid

on SQL2008R2 x64

The media family on device '<backup_file_location>' is incorrectly formed. SQL Server cannot process this media family.

Verfity database is terminating abnormally.

Bazinga!

sqlnickxu SSCertifiable Points: 5026 More actions · Answer 2

And I also tried to run "restore verifyonly from disk = <UNC_location_of_original_sql2000_backupfile>" from SQL2008R2 server to a network UNC location (after properly assign the service account's access privileges), but got the same errore message:

The media family on device "\\<sql2000_servername>\e$\<backupfilename.bak>" is incorrectly formed. SQL Server cannot process this media family.

Verify database is terminating abnormally.

Bazinga!

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 3

Removed

Edit: replied quickly.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

sqlnickxu SSCertifiable Points: 5026 More actions · Answer 4

Thanks for your reply first of all.

But please do read carefully about my question, I am trying to restore a backup from SQL2000 to an instance running SQL2008R2, it is anything but a downgrade.

Bazinga!

M&M SSC-Insane Points: 21700 More actions · Answer 5

Is that SQL2000 backup - a native backup file or taken using some third party tool?

M&M

sqlnickxu SSCertifiable Points: 5026 More actions · Answer 6

Very native, directly from SQL Server Enterprise Manager.

Bazinga!

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 7

Odd. It could be something obscure, page size difference or something daft like that?

It's long winded but have you tried restoring to a quick install of SQL 2005 express, then backing it up again using 2005 and importing to 2008 R2?

This might mod the structure of the backup files enough for 2008R2 not to read it as Klingon.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

sqlnickxu SSCertifiable Points: 5026 More actions · Answer 8

I think I've found the cause and it has something to do with RedGate's hyperbac.

I've recently taken over this SQL2000 server and hyperbac was installed beofre I came in.

Today I used Enterprise Manager GUI to make those backups (with .bak extentions) and it turned out they are actually zipped files.

Once I use winzip to unzip them, those unzipped files are immediately recognizable by SQL2008R2 instance.

I am a new user for hyperbac, but it seems that it can tweak sql server engine to backup databases in a zipped format without explicitly alerting the user.

Bazinga!

Grant Fritchey SSC Guru Points: 398952 More actions · Answer 9

Hyperbac is a file filter. It doesn't interfere in any way with the operation of SQL Server. Instead it gets between SQL Server and the file system and runs interference to compress and decompress the files in the background. It allows you to get compression (better than SQL Server native compression) without changing any of your backup or restore code, and you get compression on SQL Server 2000 & 2005. But... you do need to know it's there, otherwise you'll run into issues attempting to restore the backups to a machine that is not running Hyperbac.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

NM

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

christopher_l_conner Newbie Points: 5 More actions · Answer 11

What a lifesaver. Hyperbac was causing the same issue in a prod environment that was recently handed to me. Couldn't restore to reporting server. You saved me many more hours of work! Thanks again!

sqlnickxu SSCertifiable Points: 5026 More actions · Answer 12

christopher_l_conner (10/10/2012)
What a lifesaver. Hyperbac was causing the same issue in a prod environment that was recently handed to me. Couldn't restore to reporting server. You saved me many more hours of work! Thanks again!

It's been awhile since I put up this post and I am glad to see it 'saved a life' 😛

Bazinga!