Sql-server – SQL Server database backup restore on lower version

sql-server

How to restore a higher version SQL Server database backup file onto a lower version SQL Server?

Using SQL Server 2008 R2 (10.50.1600), I made a backup file and now I want to restore it on my live server's SQL Server 2008 (10.00.1600).

When I tried to restore the backup onto SQL Server 2008 it gives an error i.e. Restore Failed because:

The database was backed up on a server running version 10.50.1600.
That version is incompatible with this server, which is running version
10.00.1600.

How do I restore the backup file on this server?

Best Solution

You can use functionality called Export Data-Tier Application which generates .bacpac file consisting database schema and data.

On destination server, you can use Import Data-Tier Application option which creates and populates new database from pre-created .bacpac file

If you want just to transfer database schema, you can use Extract Data-Tier Application for creating file and Deploy Data-Tier Application for deploying created database schema.

I've tried this process on different versions of SQL Server from SQL 2014 to SQL 2012 and from SQL 2014 to SQL 2008R2 and worked well.