Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question
add comment

3 Answers 3

up vote 11 down vote accepted

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.

share|improve this answer
    
+1 yes scripting it out is the only way, if you require further proof, here is technet article which answers your question: bit.ly/UYyKW –  Nick Kavadias Sep 4 '09 at 11:06
add comment

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%5Fkehayias/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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.