How to restore SQL Server 2008 database to SQL Server 2005

Tracy King updated on Apr 09, 2019 to Backup Utility | How-to Articles

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

EaseUS Todo Backup Advanced 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.

SQL Server Backup and Disaster Recovery Software

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: EaseUS Todo Backup Advanced Server.

 
 

100% of people found this article helpful.

 

Frequently Asked Questions

Why choose EaseUS Todo Backup?

"The easiest way to protect your data and system files."techradar

"EaseUS Todo Backup Free is one of the most popular free data backup applications available. This software offers a myriad of features and is extremely user-friendly. It provides a full system backup, meaning you can back up your entire operating system."TopTenReviews

"EaseUS ToDo Backup Home is a great backup program, well suited to the home and SMB space."PCWorld

What is the difference between Clone and Backup? In what cases should I use Clone?

Clone will copy one disk to another or one partition to another (unallocated space is also supported). Usually, this option is used to upgrade the current disk by creating an identical copy. Backup will generate a .PBD image file containing the selected files or system that can be restored from the .PBD image if needed.

What is the difference between System Backup and Disk Backup?

System Backup will auto-select the system boot related partition(s) as the backup source to make sure the system image can be restored properly. Disk/Partition Backup will allow you to manually select a whole disk or speficy a certain partition as the backup source. You will get the same backup result as System Backup if you manually select the system and boot partitions with the Disk/Partition Backup option.

As a computer newbie, what features should I use to protect my computer?

You can use System Backup to back up the system and boot partitions, use Disk/Partition backup to back up the data partition and add plans to run an automatic backup. If you use the computer frequently or have software that may cause boot failure of the computer, we would suggest you back up the computer every 1 to 2 days. For other situations, you can backup the computer once a week. In addition, you can schedule File Backup to back up the files which are changed frequently.

What kind of storage service should I use to save my backup images?

EaseUS Todo Backup can save backup images to internal disks, external disks, network drives, or free cloud(Google Drive, Dropbox, and OneDrive) and CD/DVD. It is suggested to save the images to the internal/external/network drives to save time as those kinds of disks have a better read-write speed. It is suggested NOT to save the image on the source disk, because once the disk is damaged, all the files on the disk will become unavailable and you will be unable to restore the image.

As a company network administrator, what features should I use to protect our internal computers?

It is recommended that you use the SQL Backup feature to back up your company's SQL server and Exchange Backup feature to back up the Exchange server. In addition, you can execute System Backup to back up the system and boot partitions on every computer and set a plan to execute the backup once a day or twice a week to ensure that the computer can be restored timely when it has boot issues. File Backup feature can be used to back up important user data. If there are a large quantity of computers, you can purchase EaseUS Backup Center to manage the backups on the client computers centrally.

About EaseUS Todo Backup

Be it a file, system, partition, or disk, EaseUS Todo Backup will help you back up or clone the item and provide you with multiple backup plans.