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.
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:
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.
. In Object Explorer
, expand Databases
a database, point to Tasks
, and then click Generate Scripts
. Follow the steps in the wizard to script the database objects.
On the Choose Objects
page, Choose Select specific database objects
, and click select all
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
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.
On the Summary page, Click Pervious
to review your selections. Click Next
to generate the script of the objects you
Connect to the SQL Server (2008 R2), and then run the SQL scripts which was generated in the previous step.
Connect to the sql server 2008 R2 database
Open the .SQL file you have generated in Step A.
Execute the .sql file.
Refresh the Database