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 )
LOG ON
( 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
Qiling Backup Server is a SQL backup and restore software for Microsoft SQL Server 2008, 2005, 2016, it also supports Exchange 2010/2007/2003 backup and restore.
Main features:
- 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: Qiling Backup.
Related Articles
- Backup Data to Tape with Tape Backup Software
- Best USB Image Tool for Windows 10/8/7 Free Download
- 7 Best Free Imaging Software for Windows 10/8/7
- How to Clone Windows 10 Operating System
- Partition Backup｜How to Backup Partition with Windows Partition Backup Software
- How to Clone M.2 SSD to Larger M.2 SSD on Laptop/Desktop [New]