Home / server / Questions / 62070
Accepted
Webjedi
Asked: 2009-09-04 08:55:44 +0800 CST

Can I restore a SQL 2008 database to a SQL 2005 server?

  • 772

I have a production box with SQL Server 2008, and I'm looking to consolidate my DEV server with another that's running 2005. So my question is, are there any options I can set that will allow me to restore the production dump of 2008 to my new dev on SQL 2005?

Thanks!

sql-server

3 Answers

  1. Best Answer

    No, you can't. The databse formats are incompatible. About the best you can do is script out the database and export the data. Note, though, that some things (like certain datatypes) that are new to SQL 2008 will throw your 2005 instance for a spin.

    • 11

  2. The simple answer is NO, you can't restore a SQL Server 2008 backup onto a SQL Server 2005 or earlier versions.

    Here is the detailed answer.

    SQL Server maintains lot of meta-data about the database (including the version it was created) in the master database's boot file and this information is read when you attach the database onto the server and checks against the version of the server. Note that this internal version is different than the version of the software like 2000, 2005 & 2008. When a lower version database is attached to a higher version (server) then immediately the internal version of the database is bumped and the restore/attach goes fine but when you attach the higher version database onto a lower then SQL Server fails the required validation. One of the reason is, the meta-data and the system internal tables and structures change from one SQL Server version to another and the lower versions can't handle the higher version structures.

    You can check this information very easily with the following command.

    DBCC DBINFO WITH TABLERESULTS

    You need to look for dbi_createVersion DBINFO STRUCTURE: DBINFO @0x467BEEE8 dbi_createVersion 655

    SQL Server 7.0 : 515 SQL Server 2000 : 539 SQL Server 2005 : 611/612 SQL Server 2008 : 655

    http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/07/28/database-version-vs-database-compatibility-level.aspx

    Now coming to the solution, I have to agree with gbn that you can install a SQL Server 2008 side by side on the dev box, although its NOT too much trouble scriping the schema/data via SSIS to SQL Server 2005 instance, its not necessary to go back to a previous version and importantly you always want to keep the production and dev on the same versions with the servcie packs also otherwise you may see surprising results between dev and production.

    • 1

  3. As mentioned, no.

    Why not install SQL 2008 onto the dev box too so you have both SQL 2005 and 2008 instances. Otherwise, it'll be a complete pain if you want to copy prod to dev regularly or use SQL 2008 features.

    • 0