February 26, 2009 at 4:10 am
Hello to you all.
Im more of a Oracle dba newbie but got a delicate task from my boss to restore a sql .bak backup from which we later on will convert data into an oracle db.
So...i have this .bak file with the size of 5,3gb. I have no information how this backup is taken and from which sql server version.
Earlier on one of my co-worker tried to restore this .bak backup onto sql2000 server but it didnt go well and might be caused by sql version problem.
I have installed sql2005 enterprise ed on a w2k3 server with sp1. I have also upgraded sql2005 with sp3.
The installation was done with default options and Windows authentication.
The .bak file is stored locally on d:
When i try to restore in Management Studio i get the following error when choosing the .bak file
"The media family on device d:\........ is incorrectly formed. SQL server cannot process this media family.
RESTORE HEADERONLY is terminating abnormally. (Error 3241)"
I have tried open a query window in Management Studio and executed the following commands.
"RESTORE HEADERONLY FROM DISK = 'D:\xxxxxx.bak';"
"RESTORE VERIFYONLY FROM DISK = 'D:\xxxxxx.bak'"
Both commands generate the same error.
"Msg 3241, Level 16, State 7, Line 1
The media family on device 'd:\xxxxxx.bak' is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally."
I have tried to search information on the web but its very hard to get any conclusive results.
Is there any way to verify the .bak backup with any third party tools for inconcistensy or corruption in any way?
Or is the conclusion basically that the backup is corrupt?
February 26, 2009 at 4:20 am
Hi Robert,
You have used the correct commands to verify the backup integrity. From the error message, it looks like the backup file is corrupt.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
February 26, 2009 at 5:02 am
Thank you for the answer.
Just as i expected but my boss wouldnt take that as an answer =) "keep finding a way to verify if the backup is corrupted or not".
Btw anyone who knows if there are any third party software/tools which can verify a backup with more accurate information about the cause of corruption?
February 26, 2009 at 7:05 am
If the below command doesn't return the backup info, including compatability level (SQL version), it may very well be corrupt. If so, I know of no way to get it to restore within SQL (hopefully, for your sake, I'm wrong on that point).
RESTORE HEADERONLY
Edit - Today's question of the day would have yielded that info for you!
-- You can't be late until you show up.
February 26, 2009 at 9:15 am
robert.larsson (2/26/2009)
Just as i expected but my boss wouldnt take that as an answer =) "keep finding a way to verify if the backup is corrupted or not".
That's what RESTORE VERIFYONLY is there for. If it says the backup is not usable, it's not.
"The media family on device d:\........ is incorrectly formed. SQL server cannot process this media family.
That's not the error that restoring to a lower version usually gives
Btw anyone who knows if there are any third party software/tools which can verify a backup with more accurate information about the cause of corruption?
You'll probably need a time machine for that. 😉
Usual reasons - IO corruption, network glitch if you backed up over a network, anything else that can damage a file.
The other possibility is that it's not a SQL backup at all. See if you can find out where it came from.
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
February 27, 2009 at 12:23 pm
GilaMonster (2/26/2009)
The other possibility is that it's not a SQL backup at all. See if you can find out where it came from.
Maybe someone backed up with a third party tool (Idera, RedGate ...) then renamed to .bak
March 4, 2009 at 1:43 pm
Litespeed also one such tool
March 5, 2009 at 10:57 am
if its litespeed backup it will have a extension .BKP
I dont think its Litespeed backup.
March 5, 2009 at 11:04 am
check the backup file again.If it has .BKP extension then it can only be restored with Litespeed.
March 5, 2009 at 11:54 am
ravikanth (3/5/2009)
check the backup file again.If it has .BKP extension then it can only be restored with Litespeed.
While .bkp is a "backup" file extension, it doesn't necessarily mean it's Litespeed. I haven't used Litespeed in 2+ years, but I thought it, like SQL server, allowed you to change the extension to whatever you wanted via the GUI. I do know for a fact, it you scripted the backup to TSQL, you could change the extension to anything you wanted - the GUI, I'm a little fuzzy on.
-- You can't be late until you show up.
March 6, 2009 at 8:47 am
Just to keep you informed =)
The backup .bak was copied to a usb memory stick and then copied to the server where we try to run the restore job.
Maybe that might be causing the problem.
Thanks for your help guys (and girls).
June 18, 2009 at 1:32 pm
I just had the same problem this morning. Same exact symptoms and I tried everything on this post. I just installed SP3 and the problem is solved.
June 21, 2009 at 5:31 am
Litespeed backup not necessarilk .BKP , we are using it on daily basis in our PROD environment too 🙂
July 14, 2009 at 6:12 am
This problem came up on my network today (jul 14 09). I checked the options for compatibility level and sure enough (2008 SQL) it was set to 100. I changed it to compatibility level 80 (SQL 2000) and BINGO - restore worked. It may have to do with the fact that on the server level, I have the default set ot use compressed backups which is not a feature of 2000. It seems to me I saved the backup using 2005, but I believe the compat level on 2005 was set to 2000.
So, for kicks, you may want to try to adjust the compatibility level and ignore the error message. Someone already submitted a bug on this one... if what I did works for you, you might want to head over to Connect.Microsoft.com and let them know this happened to you also:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=329081
Jamie
August 20, 2009 at 4:31 am
I've just had the same problem with a SQL backup that had been created in SQL 2008. Probably a bit late for your boss now, but worth checking if you get this message.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply