Home / server / Questions / 148445
In Process
Mario
Asked: 2008-09-16 08:27:28 +0800 CST

SQL Server restore a backup results in an error

  • 772

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?

sql-server sql-server-2005 restore

7 Answers

  1. 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.

    • 7

  2. 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.

    • 5

  3. 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.

    • 3

  4. 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.

    • 1

  5. 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.

    • 1

  6. (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............?

    • 1

  7. 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.

    • 0