Restoring a backup to an older version of SQL Server

Question

When trying to restore a backup to a SQL Server Express 2008 database I got the following error:

Restore failed for Server '...\SQLEXPRESS'. (Microsoft.SqlServer.SmoExtended)
System.Data.SqlClient.SqlError: 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.2531. 
Either restore the database on a server that supports the backup, or use a backup 
that is compatible with this server. (Microsoft.SqlServer.Smo)

Is there a way to get a backup which is compatible with the older (in my case 10.00.2531) version from the newer (in my case 10.50.1600) version of SQL Server Express?

• serge: try this solution dba.stackexchange.com/a/289341/227324

Answer 1

You'd have use the Import/Export wizards in SSMS to migrate everything

There is no "downgrade" possible using backup/restore or detach/attach

• serge: there is an alternative, and more clear solution, here dba.stackexchange.com/a/289341/227324
• RolfBly: 11 years after the OP, this comment is now the best answer to the question.

Answer 2

You can use the Import/Export wizard to move data between databases.

Right click on the database you want to export, choose "Tasks", then "Export Data". The wizard will guide you through the process.

You're right, though. You won't be able to do a backup/restore moving from SQL Server 2008 R2 to SQL Server 2008 Express.

Answer 3

SQL Server backup doesn't support backward compatibility

Steps to get DB on older versions -

• For Schema: right click on your database --> Tasks --> generate scripts -->next-->next--> click on advanced button --> change the option "type Of Data to script" to "schema" -->ok -->next -->next

• For Data: right click on your database --> Tasks --> generate scripts -->next-->next--> click on advanced button --> change the option "type Of Data to script" to "data" -->ok -->next -->next

• Iman: One problem with this method is for big databases (many tables, and huge data) running scripts to restore takes long time. but it is handy and easy

Answer 4

The only way is to create a new database on the 10.00.2531 version and import the data from 10.50.1600.

You can't restore down to an older version.

Answer 5

right click on your database --> Tasks --> generate scripts -->next-->next--> click on advanced button --> change the option "type Of Data to script" to "schema and data" -->ok -->next -->next so now you have a script that creates database and its table and fills the data in it :) ;)

• Thomas Rushton: Depending on the amount of data, that script could be enormous...
• ErikE: @ThomasRushton That script is by definition going to be bigger than the database itself, probably by at least double if not worse! I hope it's not a 50 GB database...
• Burgi: This is a duplicated answer
• juFo: this is not a duplicate answer, it is a great answer at the type of data is "schema AND data" ! ;-) which is what I needed!
• basher: @Burgi this answer is older than dsingh's. He simply copied Sarah's and added 2 options, 1 for schema the other for data. His is the duplicate.