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:
- Login to dev, open SQL Server Management Studio
- Right click on the database | Tasks | Backup. Keep all default options (full backup etc.)
- Move .bak file locally to prod (no network drive), login to prod, open SQL Server Enterprise Manager.
- Right click Databases node | All Tasks | Restore database.
- Change Restore as database to reflect the same database name.
- Click radio button 'From device'. Click 'Select Devices'
- 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?