Sql-server – Easiest Way to Convert a SQL Server 2008 Database Down to SQL Server 2005

sql server

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?

Thanks!

Best Answer

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.

Related Topic