Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have a database in dev (SQL Server 2005 on Windows Server 2008) that I need to move to prod (SQL Server 2000 on Windows Server 2003). My process is as follows:

  1. Login to dev, open SQL Server Management Studio
  2. Right click on the database | Tasks | Backup. Keep all default options (full backup etc.)
  3. Move .bak file locally to prod (no network drive), login to prod, open SQL Server Enterprise Manager.
  4. Right click Databases node | All Tasks | Restore database.
  5. Change Restore as database to reflect the same database name.
  6. Click radio button 'From device'. Click 'Select Devices'
  7. Click Restore from: Add..., browse to .bak file (small - only 6mb)

Now I am ready to restore the database, so I click OK and get the following error:

"The media family on device 'E:...bak' is incorrectly formed. SQL Server cannot process this media family. RESTORE DATABASE is terminating abnormally."

This error is immediate.

I have tried a few different variations of this - restoring the db to dev machine with a different db name and log file names (where it originated), creating an empty database with the same physical path to files before and trying to restore to that, making a few different .bak files and making sure they are verified before uploading them to prod. I know for a fact the directory for the .mdf and .ldf files exist on prod, though the files themselves don't exist. If, before I click OK to restore, go to the options tab instead I get the following error:

Error 3241: The media family on device 'E:...bak' is incorrectly formed. SQL Server cannot process this media family. RESTORE FILELIST is terminating abnormally.

Anyone have any bright ideas?

share|improve this question

migrated from stackoverflow.com Jun 5 '10 at 6:19

This question came from our site for professional and enthusiast programmers.

    
I agree, just spent a day on this and wasted my time, it seems like a backward compatibilty issue, 2k8 R2 to 2k5 in my case. –  user61778 Jan 31 '11 at 1:38

8 Answers 8

The backup headers in MSSQL2008 are different than those in MSSQL2005, which is likely the source of your problem. Try exporting the DB instead of the backup-restore paradigm, or set up MSSQL2008 on your DEV server.

share|improve this answer

As Mike Dimmick says - SQL 2000 won't recognise SQL 2005 backups (like Word 2000 wouldn't recognise 2007's DOCX files). Therefore you'll have to migrate your data in a lower common denominator format such as SQL scripts. Try the Database Publishing Wizard which is part of the SQL Server Hosting Toolkit which generates a single SQL file for both schema and data.

share|improve this answer
1  
It's important to check if you have SqlExpress installed (you may have it installed without realizing you have it). It is very easy to log into SqlExpress by mistake and then panic because it won't let you restore a 2008 database to it. This is probably a very common reason for getting this error. –  Simon Apr 8 '10 at 23:43

You can move backups forward in the SQL Server family, i.e. 2000 to 2005 or 2008, but not backward: you cannot restore a SQL Server 2005 backup on SQL Server 2000.

share|improve this answer

It's possible it's a permissions issue keeping the file from being created when you try to restore. Try creating a new database on the target with the same name that you want and then restoring over the top of it.

Also, if you are trying to restore the backup from an external hard drive or a network share try copying the backup file to the local machine before attempting the restore.

share|improve this answer

By default there are two object explorers from SQL Server 2005. I guess you are trying on server/sqlexpress object. Try to restore to another one.

share|improve this answer

(Relieved sigh.) Ahhhhh, that's the problem.

I have SQL Server 2008 SP1 running on a remote PC. I backed up it's database, but couldn't get my local copy of SQL Server 2008 to restore from the .bak file.

As [Yini] says, this was down to my copy of SQL Server 2008 opening my SQL Server 2005 instance on my local PC, rather than my SQL Server 2008 instance. Genius.

And I completely agree with [Yini] - fair enough, you can't restore a SQL Server 2008 backup into a 2005 instance, but Microsoft could, at least, give us a decent error message.

*"The media family on device 'D:\DatabaseBackup_21_02_2011.bak' is incorrectly formed. SQL Server cannot process this media family."*

My backup wasn't incorrectly formed. It just can't be restored into a SQL Server 2005 instance, even when I'm running SQL Server 2008 SP1.

Is it really that difficult to put this into an error message............?

share|improve this answer

A few thoughts:

  • Approx what size is the database you're restoring?
    The only support article on this error is for SQL 2000 and talks about large log files. While you're not on SQL 2000 it might help to try to reduce the size of the logs first if they are particularly large.

  • Are you able to restore the database onto your Dev server (as a different db name, to different files)? That would prove whether the file itself is fine so far as SQL 2005 is concerned.

  • Are you able to restore any other databases from that Dev server to Prod?

share|improve this answer

Hey everyone, as Mike suggests I would recommend the Database Publishing Wizard, it does a beautiful job.

Here is a workaround I initially used that I wouldn't entirely suggest - it seems a bit ghetto.


1) Create the db on prod, create a db user with proper rights, open port on your firewall that allows remote access
2) From dev in SQL Server Management Studio, right click the db -> tasks -> export data. When selecting destination, make sure to specify port xxx.xxx.xxx.xxx,1764.
3) Select all tables to transfer, click next 100 times, and you are done.

WARNING: This doesn't copy over stored procedures.
WARNING: This doesn't copy over Identities for int columns

UPDATE: It looks like you cannot go from 2005 to 2000. I found the following post link text - it sounds like you can using my methods above, but you also want to script all objects and then run that on 2000.

share|improve this answer

protected by Community Aug 3 '11 at 22:03

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.