Backup SQL Server 2008 R2 Restore on SQL 2008

Question

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?

---

Ian Ceicys

Answer 1

Sorry but you cannot restore a laster version backup to a previous version. It is only forward compatible.

An alternative would be to export your database on the previous version.

---

Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.

Answer 2

NO you can not. Eventhough "R2" has SQL 2008 in it,  it has a different version number and so you can not restore backup of R2 database on SQL 2008 SP1

http://www.sqlskills.com/BLOGS/PAUL/post/SQL-Server-2008-R2-bumps-the-database-version.aspx

Alternative is to script out all data and database objects and run those scripts on SQL 2008

Answer 3

As the others have said, you can't do that, once you upgrade the database by attaching/restoring it to a higher edition, it is a one way process.  I blogged one way to downgrade using SMO and C# here:

http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/11/downgrading-from-sql-2008-to-2005.aspx

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

Answer 4

Try this:

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.
    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.
12. Once the .sql file is organised, parse it [just to sure], and then execute.

Answer 5

Thanks,

That is a great tip.

Answer 6

Try this:

 

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.
    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.
12. Once the .sql file is organised, parse it [just to sure], and then execute.

 

It worked like a charm for small db. But I have an issue with larger one. It is 2.2GB db which is on SQL Express 2008 R2 right now. I want to migrate/copy/restore to our production SQL which is SQL 2008 version. Is there any possibility to do so? I am new to SQL and will be really thankful for any help.

 

Best,

 

Answer 7

Hi All,

 

It worked for me but i have size limitation issue, the script size is about 125MB how to over come this .

is it possible to to take the backup of table,object and data separately and restore it one by one.

Answer 8

You have to be kidding me. R2 is almost the same thing as R1.

Answer 9

it's cause microsoft don't think of things to make it easier.  its easier to do this on open source.  how lame...