SQL Server database backup restore on lower version
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?
Solution 1:
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.
Solution 2:
No, is not possible to downgrade a database. 10.50.1600 is the SQL Server 2008 R2 version. There is absolutely no way you can restore or attach this database to the SQL Server 2008 instance you are trying to restore on (10.00.1600 is SQL Server 2008). Your only options are:
- upgrade this instance to SQL Server 2008 R2 or
- restore the backup you have on a SQL Server 2008 R2 instance, export all the data and import it on a SQL Server 2008 database.
Solution 3:
You can not restore database (or attach) created in the upper version into lower version. The only way is to create a script for all objects and use the script to generate database.
select "Schema and Data" - if you want to Take both the things in to the Backup script file
select Schema Only - if only schema is needed.
Yes, now you have done with the Create Script with Schema and Data of the Database.
Solution 4:
Will not necessarily work
Backup / Restore - will not work when the target is an earlier MS SQL version.
Copy Database - will not work when the target is SQL Server Express: "The destination server cannot be a SQL Server 2005 or later Express instance."
Data import - Will not copy the schema.
Will work
Script generation - Tasks -> Generate Scripts. Make sure you set the desired target SQL Server version on the Set Scripting Options -> Advanced page. You can also choose there whether to copy schema, data, or both. Note that in the generated script, you may need to change the DATA folder for the mdf/ldf files if moving from non-express to express or vice versa.
Microsoft SQL Server Database Publishing Services - comes with SQL Server 2005 and above, I think. Download the latest version from here. Prerequisites:
sqlncli.msi/sqlncli_x64.msi/sqlncli_ia64.msi,SQLServer2005_XMO.msi/SQLServer2005_XMO_x64.msi/SQLServer2005_XMO_ia64.msi(download here).
Solution 5:
Here are my 2 cents on different options for completing this:
Third party tools: Probably the easiest way to get the job done is to create an empty database on lower version and then use third party tools to read the backup and synchronize new newly created database with the backup.
Red gate is one of the most popular but there are many others like ApexSQL Diff , ApexSQL Data Diff, Adept SQL, Idera …. All of these are premium tools but you can get the job done in trial mode ;)
Generating scripts: as others already mentioned you can always script structure and data using SSMS but you need to take into consideration the order of execution. By default object scripts are not ordered correctly and you’ll have to take care of the dependencies. This may be an issue if database is big and has a lot of objects.
Import and export wizard: This is not an ideal solution as it will not restore all objects but only data tables but you can consider it for quick and dirty fixes when it’s needed.