I have a SQL Server 2008 database that I need to bring into my environment, but my production servers are all running SQL Server 2005. Upgrading the environment is not an option, at least not in the near term.

I do, however, have non-production SQL Server 2008 boxes to use if they can help out during the conversion.

The database is 1GB in size.

How do I handle this conversion?



See this similar question regarding 2005 to 2000.

The only way to do it is to restore to a 2008 server, script out the 2008 database, then run the script on the SQL2005 server.

The good news is that SQL 2008 Management Studio makes this super duper simple, even giving you an option to include data in the scripts. Just right-click the database, go to Tasks, and click "Generate Scripts". Walk through the wizard, and make sure to choose the option to include data.

EDIT: I looked more into the technical details of why this can't be done using other methods (restore from backup, or via attaching), turns out there are two different properties of a database, the compatibility level and the database version: (excerpted from SQL Storage Engine Blog)

  • The database compatibility level determines how certain database behaviors work. For instance, in 90 compatibility, you need to use the OUTER JOIN syntax to do an outer join, whereas in earlier compatibility levels, you can use '=' and '='.

  • An upgraded database retains its compatibility level. For example, a database that was created on SQL Server 2000, and didn't have its compatibility level altered, will stay in 80 compatibility level when its upgraded to SQL Server 2005.

  • The database version is a number stamped in the boot page of a database that indicates the SQL Server version of the most recent SQL Server instance the database was attached to.

  • SQL Server is not "up-level compatible". You cannot attach a database that was created on (or has been upgraded to) SQL Server 2005 to any earlier version of SQL Server (also true for trying to attach a 2000 database to 7.0, and so on).

  • You cannot attach a database that was created on an earlier version without going through the proper upgrade procedures. Forcibly attaching a database using various hacky methods will result in all kinds of weird errors, and possibly crashes.

  • Of course, it'll be a rather large script :) – BradC Oct 5 '10 at 20:26
  • 2
    +1 Thanks! I was worried that this procedure would be cumbersome, but it went just fine. – Michael Gorsuch Oct 6 '10 at 13:33

OK - my below answer is wrong. I'll leave it up because the discussion of why I was wrong is certainly valuable.

If the DB doesn't depend on any specific features of 2008, try setting the compatibility level to 2005 (90) and then do the backup and restore to your server.

I'm not sure if you can do this if you have been handed a DB BAK file already done.

If it does depend on specific features of 2008, you're SOL without a DBA and a developer to help you.

  • sorry, that's not possible. – BradC Oct 5 '10 at 20:18
  • You cannot do this in this way. There is no easy method. See this post blog.sqlauthority.com/2008/10/16/… – Robert Kaucher Oct 5 '10 at 20:19
  • @BradC What of that is not possible? Are you unable to change the compatibility level of the server? – Darth Android Oct 5 '10 at 20:20
  • Regardless of compatibility level, you can't restore a backup that was created on 2008 on a 2005 server. Full stop. Can't be done. – BradC Oct 5 '10 at 20:23
  • That is correct. You cannot lower the compatability level of A DB once it has been set. – Robert Kaucher Oct 5 '10 at 20:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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