July 7, 2011 at 9:43 am
I'm tearing my hair out over a restore I've done every month for the last couple years. The source database is on a SQL Server 2005 sp3 server and the target database is on a SQL Server 2008 server. The source database is compressed using WinRAR. The source database is restorable on the same server, so I believe the source database backup is fine. When I try to restore it onto the SQL 2008 server I get the error "The media family on device xxxx' is incorrectly formed. SQL Server cannot process this media family.
RESTORE HEADERONLY is terminating abnormally. (.Net SqlClient Data Provider)
MicroSoft SQL Server, Error: 3241
Things I've tried:
Rule out WinRAR:Creating a tiny database on the source server and emailing it to me. Get same error.
Different transfer methods in WinRAR: Tried Ascii, Binary, and Auto but get same error.
New backup copy: Sent 3 new backup copies, same result
Verify database version on source: Source is SQL Server 2005 sp3, Target is SQL Server 2008 sp1 (both standard edition 64 bit)
Try new target server: Tried to restore onto a SQL Server 2005 box, still same error
Any suggestions would be greatly appreciated! Thanks. I'm running out of ideas. 😉
July 7, 2011 at 11:06 am
Have you tried copying the mdf and ldf to the target server and mounting those files to the target instance?
Have you tried checkdb?
How about chkdsk on the source disk system?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 7, 2011 at 1:14 pm
Can you provide the backup statement that is being used to backup the databases?
Also, just to verify - can you provide the results of @@version for each instance?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 7, 2011 at 2:19 pm
This is a restore we've done through SSMS gui every month for years. We have verified that the source is SQL 2005 SP3 and target is SQL 2008 SP1. The source is remote so I don't have access to drive info. We're going to try to detach the remote database and attach it here. I'll post again after I try the detach attach process.
Thanks for the tips!
July 7, 2011 at 3:23 pm
are you copying the backup over an existing file of the same name? If so, try deleting the file on the 2008 server first. then recopy from sql2005.
I have found in the past a file constantly overwritten can get corrupted and not be recognised as a backup file anymore. Delete and recreate fixes it.
---------------------------------------------------------------------
July 7, 2011 at 4:48 pm
Boy, that was an ordeal. We finally got it to work by detaching it from the source server, ftping it, then renaming it and attaching it on the target server. I used this link: http://msdn.microsoft.com/en-us/library/ms174385(v=SQL.90).aspx for attaching a database using only one file. Whew!
Thanks so much for all the suggestions! It's greatly appreciated. Now my head feels better because I can stop bangining it against the wall.:-)
July 8, 2011 at 6:12 am
Elizabeth.Block (7/7/2011)
finally got it to work by detaching it from the source server, ftping it, then renaming it and attaching it on the target server.
no need to detach the source database, simply take the source offline and this will allow you to copy the disk files off the server
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 8, 2011 at 9:34 am
Great news.
As Perry said - just take the db offline next time and copy the files.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 8, 2011 at 9:36 am
Thanks all for the great help! I'm breathing easier now....
July 8, 2011 at 9:40 am
.. and copy data and log files (you said you had to do a single file attach)
---------------------------------------------------------------------
July 8, 2011 at 9:44 am
george sibbald (7/8/2011)
.. and copy data and log files (you said you had to do a single file attach)
Excellent point George. Copy both the log and data files and you should be better off.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 8, 2011 at 10:00 am
Elizabeth.Block (7/7/2011)
This is a restore we've done through SSMS gui every month for years. ....
Why aren't you using a sqlagent job with a job proxy account that can read from the original backup file location and schedule or launch that at your specs ?
Maybe even just add a backup database ... with copy_only for this purpose and restore that bak file.
No need to take the db offline.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 8, 2011 at 10:00 am
This is a fairly static database, only updated once a month, at which time we get a compressed (RAR) file from the organization. That organization has a process where they send only the data file to many customers. I am not in charge of their process so I have to work with what they give me. Is there some risk in only getting a data file and not the log file if the data hasn't changed in a month?
July 8, 2011 at 10:42 am
my apologies didn't know that.
Yes there is a risk with that. One day the file just won't attach and you will have to go through a complex hack that risks data loss. Both data and log file should always be sent, the databases is incomplete without its log file.
I would say you are entitled to ask that both files be sent. But preferable a .bak file should be provided.
---------------------------------------------------------------------
July 8, 2011 at 10:44 am
though didn't you originally say you were trying to do a restore?
---------------------------------------------------------------------
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply