SQL Server restore a backup results in an error

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?

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

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

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

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