locked
Can not open Database in SQL Server 2008 RRS feed

  • Question

  • I created a backup to a database on SQL SERVER 2008 R2. I can not open it in SQL Server 2008 (on my laptop).
    Regards,
    N_SQL
    Unfortunate is he who cannot gain a few sincere friends during his life
    ...and more unfortunate is the one who has gained them and then lost them (through his deeds).
    Thursday, June 24, 2010 4:00 AM

Answers

  • You can't restore a database to a previous version of SQL Server. Each database has a version number. If you execute SELECT name, version FROM sys.sysdatabases you will see it. SQL Server 2008 R2 is version 661. But SQL Server 2008 is version 655. The Database Engine uses the version number to prevent a newer version database from being opened by an older version of the Database Engine. Imagine what would happen if a database with an xml data type from 2008 was opened in SQL Server 2000. The Database Engine wouldn't be able to read the file, because the file format changed.

    So the current Database Engine understand the older versions (within limits), and can open them. But then it converts it to the newest version. But old versions of the Database Engine can't open newer versions, because 2008 didn't know what we were going to do in 2008 R2 (in 2010).

    So how can you get the database from 2008 R2, to 2008? Use the Copy Database Wizard in Management Studio.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Proposed as answer by Tom Li - MSFT Tuesday, June 29, 2010 3:04 AM
    • Unproposed as answer by Tom Li - MSFT Tuesday, June 29, 2010 3:06 AM
    • Proposed as answer by Tom Li - MSFT Tuesday, June 29, 2010 3:21 AM
    • Marked as answer by Tom Li - MSFT Wednesday, June 30, 2010 1:53 AM
    Thursday, June 24, 2010 3:41 PM
    Answerer

All replies

  • Can you be more specific? What is the error are  you getting?
    Best Regards, Uri Dimant http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, June 24, 2010 5:45 AM
    Answerer
  • Verify the following command to check the backup file health.

    RESTORE VERIFYONLLY FROM DISK = ‘<BACKUPFILE PATH DRIVE>

    -----------------

    Thanks,

    RajaSekhar Reddy .K

    Thursday, June 24, 2010 6:31 AM
  • You can't restore a database to a previous version of SQL Server. Each database has a version number. If you execute SELECT name, version FROM sys.sysdatabases you will see it. SQL Server 2008 R2 is version 661. But SQL Server 2008 is version 655. The Database Engine uses the version number to prevent a newer version database from being opened by an older version of the Database Engine. Imagine what would happen if a database with an xml data type from 2008 was opened in SQL Server 2000. The Database Engine wouldn't be able to read the file, because the file format changed.

    So the current Database Engine understand the older versions (within limits), and can open them. But then it converts it to the newest version. But old versions of the Database Engine can't open newer versions, because 2008 didn't know what we were going to do in 2008 R2 (in 2010).

    So how can you get the database from 2008 R2, to 2008? Use the Copy Database Wizard in Management Studio.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Proposed as answer by Tom Li - MSFT Tuesday, June 29, 2010 3:04 AM
    • Unproposed as answer by Tom Li - MSFT Tuesday, June 29, 2010 3:06 AM
    • Proposed as answer by Tom Li - MSFT Tuesday, June 29, 2010 3:21 AM
    • Marked as answer by Tom Li - MSFT Wednesday, June 30, 2010 1:53 AM
    Thursday, June 24, 2010 3:41 PM
    Answerer
  • Is there any way to make a SQL SERVER 2008 Compatible backup?
    Regards,
    N_SQL
    Unfortunate is he who cannot gain a few sincere friends during his life
    ...and more unfortunate is the one who has gained them and then lost them (through his deeds).
    Wednesday, July 14, 2010 6:49 AM
  • No, you cannot downgrade the version
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, July 14, 2010 7:35 AM
    Answerer