locked
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
    Thursday, July 22, 2010 1:59 PM

Answers

All replies

  • 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.
    Thursday, July 22, 2010 2:46 PM
  • 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

    Thursday, July 22, 2010 2:50 PM
  • 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!
    Friday, July 23, 2010 12:01 AM
  • 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.

    • Proposed as answer by TheCTOSian2 Saturday, August 14, 2010 5:50 PM
    Saturday, August 14, 2010 5:50 PM
  • Thanks,

    That is a great tip.

    Wednesday, September 01, 2010 2:33 AM
  • 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,

     

    Monday, September 20, 2010 8:04 AM
  • 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.

    Thursday, December 09, 2010 12:29 PM
  • You have to be kidding me. R2 is almost the same thing as R1.
    Tuesday, December 21, 2010 9:16 PM
  • it's cause microsoft don't think of things to make it easier.  its easier to do this on open source.  how lame...
    Wednesday, February 02, 2011 5:01 PM