Compatibility problem: an "sql server 2008 R2" db backup cannot be restored to an "sql server 2008" server??

Question

To verify if the "2008 R2" version can be restored back to a "2008" dbserver I make an experiment:  construct a database on R2 version ( version : 10.50.xxx )  and backup it; then I try to restore the db backup to a "sql server 2008" db-server ( version : 10.0.xxx ) but get failed.

Error message is as shown:

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.

 

Originally I want to upgrade a "2008" server to "2008 r2" , but this problem withdraws me: if I do so , I am afraid all the existing clientuser's dbs cannot be backed up to themselves if they don't have "2008 r2" locally:   if a database is backed up under "2008 r2" environment whereas client user only has "2008" db server, does it mean he cannot restore it anymore?  

So I wonder if there is an approach that helps me to create a "backward-compatible" database; when creating database on wizard there is an option to choose "compatibility level" but I don't think it meets my requirement..    Anyone can help me on this ??  

 

Answer 1

Hi Atlas,

 

If I understand correctly, you want to restore a database created in SQL Server 2008 R2 to SQL Server 2008.

But, be design, we can only restored a backup database to a SQL Server that has version higher than the original SQL Server.

 

So, if the database is  with a small amount of data, we can generate scripts for the objects from SQL Server 2008 R2, create these objects in  SQL Server 2008 using the scripts we generated before, and then use BCP in/out the data.

Or we can use SQL Server Integration Services to transfer data from SQL Server 2008 R2 to SQL Server 2008.

 

For more information about how to generate scripts, please visit the following link:

http://msdn.microsoft.com/en-us/library/ms178078.aspx

 

If you have any more questions, please feel free to let me know.

 

Thanks,

Yoyo Yu

Answer 2

Submit and find another way..... and here it is http://sqlbackupandftp.com/  seems its a frequent operation/feature to backup and restore to various revisions.

 

W

Answer 3

Hello Everyone,

The reason for the failure is the internal version number of SQL Server 2008 R2 is different from that of an SQL Server 2008 or lower versions. Since the internal version number of SQL Server 2008 R2 is higher than that of an SQL Server 2008 database the restore process fails. For more information check the following two articles.

1.       Why an SQL Server Database from a higher version cannot be restored onto a lower version of SQL Server?

2.       How to identify if the database was upgraded from a previous version of SQL Server

3.   How to Downgrade SQL Server Database From a Higher Version to Lower Version

Hope that Helps!

Thanks
Ashish Kumar Mehta

Please click the Mark as Answer button if a post solves your problem!

Answer 4

Backup SQL Server 2008 R2 Restore on SQL 2008

http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/4daae3e5-15ab-4929-a122-e1bab420310e?prof=required

Query From:

Ian Ceicys

I have a SQL Server 2008 R2 instance that I need to backup and restore onto a SQL 2008 instance.

When I attempt to restore the back up (.bak) database from the R2 SQL server onto the SQL 2008 server I get the following message:

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)

How can i backup the SQL 2008 R2 database to be compatible with SQL 2008?

Good Answer by:

TheCTOSian2

1. Install an instance of SQL 2008 R2 on one machine and SQL 2008 on another
2. Open SQL Server Management Studio R2
3. Select your source Database
4. Select Tasks>Generate Scripts.
5. Select 'Script entire database and all database objects', press 'Next'
6. Select 'Save to File' and click on the 'Advanced' button
7. Select 'Script for Server Version' and select the version you want: 200/2005/2008
8. Select 'Type of data to Script' and select Schema/Data/both 
9. Click 'OK',Next and do it!
10. Copy the resulting file to the target machine with SQL 2008 [or whatever]
11. Log onto your SQL Management Studio and open the copied .sql file... be aware that there may be limitations on the file size.
12. There may also be issues with the order that the .SQL file inserts the data into the target database and if there are FK constraints in place, this could be an issue.... simply re-order the insert lists.
13. Once the .sql file is organised, parse it [just to sure], and then execute.

 

The above steps does work good if the db size is small. If my db size is 1 gb or above, the text file is much larger. In this case the steps above is of no use. Could someone provide me with a better option to do the same thing.

 

---

Kasim Husaini

Answer 5

YOYO YOU,

 

You stated you could you use SQL integration services to transfer data from SQL 2008 R2 to regular 2008, how would I go about doing that?

Answer 6

This being "by design" is such bs. Microsoft should allow the later version to save as older version like in MS Word or Excel. Just remove the features that were not available in the older versions and add a warning that everything might not work as expected. This abrupt cutting off of all DB backup/restores is absolutely idiotic. Whoever is making these decision in the SQL team should get fired.

I have clients with prod environments with SQL Server 2008 and 2008 R2. My Dev environment has the latest VS 2010 and SQL 2008 R2. Because of this incompatibility I will be forced to downgrade my dev machine or go tell my clients with the older versions that unless they shell out for the latest version, I can't support them.

While on the subject, I've been using SQL Server since version 6.5. Up until SQL 2000, the wizards were fantastic! You just right-click anywhere and you can export just about anything to the other server. Now the import/export wizard only moves tables and drops all my keys and constraints!

This trend is indicative of a company wanting to make a quick buck at its customers' expense. No apologies nor any plans to course correct. It's decisions like these that tick off the public...by design.

Answer 7

Total agree! There is a little problem firing the person making idiotic decision. Nobody remain :) When they say "by design" as an excuse for bad decision, it mean only one thing - bad design.

After all, it is Microsoft. What do you expect?

Answer 8

This is why Microsoft are continuing to lose customers to other more flexible platforms. Your statement:

"After all, it is Microsoft. What do you expect?"

is absolutely spot on. MS as an organisation has taken the "Do it our way, or take the highway" approach for far to long. My advice is to take the highway, ie. find another vendor to utilise whenever possible. Using inflexible tools like this is what gives IT departments a bad reputation with business departments. Hopefully their arrogance and stupidity will see MS the company eventually go the way of the many great products they have purchased and mismanaged into the ground.

Answer 9

hi,

I follow bellow link and successful.

ttp://yrushka.com/index.php/database-recovery/restore-sql-2008-r2-database-on-sql-2008-sp1-instance/

Hope it help