SQL Restore Problem

I create backups of an SQL database from a Windows Server 2008 using MS SQL 2008. I can restore the backup from another server that jas Windows Server 2003 and SQL Server 2008. The problem I am having is when I try to restore the same backup (that restores correctly on these 2 servers) to a remote server that uses Server 2003 and SQL 2005. For months now I have been restoring the daily backups to this remote server (either by copying via the network), from a backup tape, or from an external hard drive and then using the GUI to restore from the backup. About a week ago, I stopped being able to restore the same backup on the remote server that I can restore on the other 2 servers. The error I am getting is "The DRbackups\Procuranightlybackup.bak is incorrectly formed. SQL Server cannot process this media family. RESTORE HEADER ONLY is terminating abnormally - MS SQL Server Error 3241)

I have tried running the backup using scripts, using the RESTOREHEADERONLY and every other suggestted way to run it that I found from previous questions from your website, from Microsoft's. The backup is NOT corrupt because I can restore the same backup on two other servers and have tried each day with different backups and still get the same error on this remote server. Please help!
tt0wnsendAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> The problem I am having is when I try to restore the same backup (that restores correctly on these 2 servers) to a remote server that uses Server 2003 and SQL 2005.

You wont be able to restore a backup taken from SQL Server 2008 machine to a machine running SQL Server 2005.
Backward compatibility is supported whereas Forward compatibility is not supported.

What I am saying is 100% correct as you have received the error message as "The error I am getting is "The DRbackups\Procuranightlybackup.bak is incorrectly formed. SQL Server cannot process this media family. RESTORE HEADER ONLY is terminating abnormally - MS SQL Server Error 3241)"

>> SQL Server cannot process this media family

SQL server 2005 will not be able to process media family or backups taken from SQL Server 2008.

Hope this helps
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Any chance that service packs are different on those machines?
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Qlemo,
     Its not a problem with Service packs..
Its the problem with the versions of SQL Server.

You can restore a backup taken from SQL Server 2005 to 2008 but you can't do the reverse case.
If you try to do it, then you will receive the error message exactly what is posted above.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> I can restore the backup from another server that jas Windows Server 2003 and SQL Server 2008.

And this confirms it too.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
tt0wnsend, you are trying to restore a SQL Server 2008 database in SQL Server 2005 instance.
SQL Server 2005 came before SQL Server 2008 so I think won't be possible to restore there the database backup you have.
Anyway check if the database backup isn't compressed because SQL Server 2005 doesn't work with compressed backups.

Good luck
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
I was confused by
"For months now I have been restoring the daily backups to this remote server (either by copying via the network), from a backup tape, or from an external hard drive and then using the GUI to restore from the backup."
and all the Windows and MSSQL versions ...

This should never have worked, for the reasons already stated (no version downgrade).

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I remembered something now. If compatibility level of that database was set to 9.0 (2005) then backup & restore could work fine during all that time. So maybe compabilitiy level now change to 10.0 (2008) and stopped working.

Can't find another logical explanation.
0
idumlupinarCommented:
Ihttp://www.devx.com/dbzone/Article/40531
http://stackoverflow.com/questions/545316/is-it-possible-to-restore-sql-server-2008-backup-in-sql-server-2005

By default you won't be able to restore SQL Server 2008 backup to SQL Server 2005, however the tutorials above might help.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development Software

From novice to tech pro — start learning today.