The following is a problem when restoring SQL 2008 backup to SQL Server 2005:
"I have been working on a database on my laptop that has SQL Server 2008 Ktma installed.
The database was restored from a full backup that I had taken from the production SQL 2005 Server.
Now that I have finished my job I want to restore it back to the production Server (SQL 2005). I took the backup with "No compression" on a disk file through the SQL 2008 Management studio.
However when trying to restore the SQL 2008 backup onto the SQL server 2005 I get an error:
The media family on device xxxx is incorreclty formed ...."
The fact that you can't restore a backup from a higher version of SQL Server on a lower version of SQL Server. How to do it? Please read on the following, I will discuss it.
1 Open SQL Server Management Studio2008. in 'Object Explorer', right click the database (StudentDB) that you want to convert. Select 'Tasks' > 'Generate Scripts...'.
2 Click 'Next'.
3 Select the database (StudentDB) that you want to convert, and check on 'Scripts all objects in the selected databases'
4 Set options:
'Script for Server Version' = 'SQL Server 2005'
'Script Data' = 'True'
'Scirpt Database Create' = 'True'
5 Select option 'Script to file', 'Single file' and 'Unicode text' on Output Option Window.
6 View summary and click 'Finish'.
7 Now you gett a complete database creation script with data. It can be executed on target database server that installed SQL Server 2005.
8 Open the generated script in SQL Server Management Studio 2005. Find the following section and change the path to proper data folder
CREATE DATABASE [StudentDB] ON PRIMARY
( NAME = N'StudentDB',
FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\StudentDB.mdf,
SIZE = 5662KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
( NAME = N'StudentDB_log',
FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\StudentDB_log.LDF',
SIZE = 7032KB, MAXSIZE = 1024GB, FILEGROWTH = 10%)
9 Execute the Script. When finished, You should get converted database of SQL Server 2005
EaseUS Todo Backup Advanced Server is a SQL backup and restore software for Microsoft SQL Server 2008, 2005, 2000.
For the detail backup and restore steps, please refer to How to backup and restore SQL Server database?
To learn more about SQL backup and recovery solutions, to better protect your database for business security, refer to SQL backup and recovery software: EaseUS Todo Backup Advanced Server.