Problem
When you are restoring your sql database from a higher version to a lower version ,you will get error like "Error: The media family on device 'c:\db_temp\1.bak' is incorrectly formed. SQL Server cannot process this media family." . The error message occurs because the SQL Server database files (*.mdf, *.ndf and *.ldf) and backups are not backward compatible.
Solution
You can use
Generate and Publish Scripts wizard of SQL Server Management Studio (2012 Express) to solve this problem. Here are the basic steps we need to follow:
A. Generate .sql file of the database schema and data from SQL Server(2012) by using the
Generate and Publish Scripts wizard of SQL Server Management Studio (2012 Express) interface.
A1. In
Object Explorer, expand
Databases,
right-click a database, point to
Tasks, and then click
Generate Scripts. Follow the steps in the wizard to script the database objects.
A2. On the
Choose Objects page, Choose
Select specific database objects , and click
select all button.
A3. On the
Set Scripting Options page, select Save scripts to a specific location and then choose the
Advanced button.In
Advanced Scripting Options dialog box, set
Types of data to script to
Schema and Data. This option is important because this is what generates the data per table.
Set
Script USE DATABASE to
False
Set
Script for Server Version to
SQL Server 2008 R2, this option is important for which version of sql server you want to restore it to.
A4. On the Summary page, Click
Pervious to review your selections. Click
Next to generate the script of the objects you
selected.
B. Connect to the SQL Server (2008 R2), and then run the SQL scripts which was generated in the previous step.
B1. Connect to the sql server 2008 R2 database
B2. Open the .SQL file you have generated in Step A.
B3. Execute the .sql file.
B4. Refresh the
Database.
Article ID: 1494, Created: August 28, 2013 at 9:32 PM, Modified: August 29, 2013 at 12:08 AM