Restore a backup on lower version of SQL Server

Arkadiusz Fronc SQL Server 25 November 2013

Have you seen this error?

Msg 3241, Level 16, State 13, Line 1
The media family on device 'c:\Program Files\Microsoft SQL Server 2008\MSSQL11.SQL2008\MSSQL\Backup\TestSQL2012.bak' is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

A few times in my life, I needed to restore a database on a lower version of SQL Server than the server where the backup was taken. It is not always easy, actually if you are reading this, I assume, your case is one of those that are not easy.

SQL Server versioning

First, to identify which case you are in, you need to know version numbers of your servers - source (where you take a backup) and target (where you want to restore the backup).

select @@version

Output consist of 4 numbers delimited by dots - 11.00.2100.60:

first number is a product version (8 means, SQL 2000, 9 means SQL 2005, 10 means SQL 2008, 11 means SQL 2012).
second number is a product level - different releases have different numbers. 10.00 is SQL 2008, 10.50 is SQL 2008 R2.
third number - it will change if you apply a hotfix or Service Pack to you SQL Server installation.
forth one is a build number

Backups compatibility

Actually, there are three cases:

Source server is an older version and target server is a newer version of SQL Server.
For example:
Source: 9.00.2234.0 (SQL 2005)
Target: 10.00.5500.0 (SQL 2008 SP2)
Then backups are compatible. You can restore a database backup on a newer version of SQL Server.
Source server and target server are the same product version and product level. They may differ on third or fourth part in the version number.
For example:
Source server is 10.50.2876.0 and target is 10.50.2799.0.
Then backups are compatible. Microsoft does not introduce significant changes in BACKUP/RESTORE functionality that breaks backward compatibility by a hotfix or Service Pack.
Source server and target server differ by first or second number - a product version or a product level and the target is older than the source.
For example:
Source: 11.00.2100 (SQL 2012)
Target: 10.00.5500 (SQL 2008 SP2)
Then backups are incompatible. Usually, changes are too significant between version that you are not able to restore a backup in such case. What is interesting, there is no way to restore even SQL 2008 R2 backup on SQL 2008 - they are the same production version!

Solution

The first and the second cases are not much interesting as there is nothing much needed to restore a backup. Much more difficult is the third case. Well ... actually the simple answer is "you cannot restore it". What do I mean by that? I mean you CAN NOT.

There are a few ways to try to work around the problem but the actual restore is impossible.

All workarounds start with the same step - scripting creation of all db objects (tables, procedures, indexes etc.). One of these descriptions can be used:

Once you have the DDL script for database structure, you can execute the script on the target server to create a database with db objects.

Workaround 1 - BCP export and import

When you have taken care of the database structure, you need to copy data. Data export can be done with BCP utility. Then import data to the objects with BCP.

Workaround 2 - SSIS

You can engage SQL Server Integration Services. This and other ways you can find on Using the SQL Server Import and Export Wizard to Move Data.

Workaround 3 - create INSERT statements

This option is not the best when your database contains many tables, but it may work when you need only a few tables to copy. You can create SELECT queries that return INSERT statements. Here is an example of such query:

select 'insert into MyTable (id, name, value) values (' + cast(id as varchar(max)) + ', ''' + name + ''', ' + cast(value as varchar(max)) + ')'
from MyTable

You can execute such queries on the source server, save the output to a file and execute the file as a script on the target server. It will insert data to apropriate tables.

These are just ideas how this problem can be worked around. Probably there are other also.