This blog post deals with the problem of restoring SQL Server Database from higher version to lower version. We generally encounter error following error.
Error while restoring database from SQL server 2008 to SQL server 2005
Msg 3241, Level 16, State 7, Line 1
The media family on device ‘<backup path>’ is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
So, There is option where you can restore database from higher version to lower version.
- Generating Script With Database : This feature enables you to create version compatible script of database. you can also generate script of database along with structure. See following screen shot step by step.
On Object Explere : Right Click Database –> Tasks —> Click Generate Script
It opens a wizard for you which begins with introduction page which provides quick brief info about script wizard.
Click Next to go to next page.
In this page you can choose specific object of database for those you want to generate script. since we need complete data. So will choose option “Script entire database and all database objects”
This page is scripting option page. In this page you can provide file name and path where script will be saved. You have multiple option as you can save script to a file or copy the script to a clipboard or directly move it to query window. In Order to generate script with data and lower version compatible script you need to set some option in advance option. See following screen.
In advance scripting option you can define “Type of data to script” I have chosen “Schema and Data” and Script for Server Version : you have Option from SQL Server 2000 to SQL Server 2008 R2. So You just need to select required version and click ok and press next on parent screen.
On clicking next you get summary screen which tell you about the options you have selected for generating script it just kind of verification and cross checking and now click ok and you are done with scripting.
Green symbols are indication that script has been generated without any warning or error. Now you can click finish and run you script in your target server.
Note : If you have VLDB ( Large Size Database) then you can use BCP. In order to move data.