How to restore SQL Server 2008 database to SQL Server 2005
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 incorrectly 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.
How to restore SQL 2008 database to SQL 2005
Step 1. Open SQL Server Management Studio2008. in 'Object Explorer', right-click the database (StudentDB) that you want to convert. Select 'Tasks' > 'Generate Scripts...'.
Step 2. Click 'Next'.
Step 3. Select the database (StudentDB) that you want to convert, and check on 'Scripts all objects in the selected databases'
Step 4. Set options:
'Script for Server Version' = 'SQL Server 2005'
'Script Data' = 'True'
'Script Database Create' = 'True'
Step 5. Select the option 'Script to file', 'Single file' and 'Unicode text' on Output Option Window.
Step 6. View summary and click 'Finish'.
Step 7. Now you get a complete database creation script with data. It can be executed on the target database server that installed SQL Server 2005.
Step 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%)
Step 9. Execute the Script. When finished, You should get converted database of SQL Server 2005
SQL backup & disaster recovery software
EaseUS Todo Backup is a SQL backup and restore software for Microsoft SQL Server 2008, 2005, 2016, it also supports Exchange 2010/2007/2003 backup and restore.
- SQL backup and restore for Microsoft SQL Server Database.
- Exchange backup and restore for Microsoft Exchange Server Database.
- Backup to tape, hard disk, network, and CD, DVD, and another removable storage device.
- One-click system backup for Windows Server.
- Schedule backup to do an automatic backup task.
- A differential backup, incremental backup to save much disk space and backup time.
For the detailed 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.
Was This Page Helpful?
Tracy joined in EaseUS in 2013 and has been working with EaseUS content team for over 7 years. Being enthusiastic in computing and technology, she writes tech how-to articles and share technical solutions about Windows & Mac data recovery, file/system backup and recovery, partition management and iOS/Android data recovery.
Taking everything into consideration, we can say that EaseUS Todo Backup Home can offer you a little extra sense of security with a set of advanced tools. The interface provides quick adaptation, every process guiding you all the way through so you don't get stuck or further damage your system rather than keeping it safe. Read More
EaseUS Todo Backup Free lets you create backups of your important files or your entire computer through its streamlined interface. Just choose the backup type you'd like to get started, and let the program do the reset. Read More
EaseUS includes all the features I expect from backup software like versioning, incremental backups, and scheduling. For more advanced users, EaseUS allows you to install a pre-boot environment onto your hard drive so you can use it to restore files without burning a CD or putting it on an external USB drive. Read More