Warning: SQL Server database files are not backwards compatibile

This is something I just learned the very hard way. Here's the scenario:

• You have a database you created on SQL Server 2005 or 2008 SP1
• You detach it from the SP1 server and attach it to SQL Server 2008 R2
• You then try to reattach it back to the original server
• You get an error about the server only supporting up to version 655 (or 612 for SQL Server 2005)

The reason is every version of SQL Server has its own file version number: SQL Server 2005 is file version 612; SQL Server 2008, either RTM or SP1, is file version 655; and SQL Server 2008 R2 is file version 660. As soon as a server touches a file it upgrades it to that server's file version. In this case once you attach the database file to SQL Server 2008 R2 it's version 660. SQL Server can read files that are at the server's version number or lower, so you can't take a SQL Server 2008 R2 database file and attach it to any prior release.

It isn't uncommon for a new releases of server software to have a new file format to support new features. The problem is SQL Server gives you no option to go back. You can't do a backup and restore, either, because the newer backup file cannot be read by the older server. At this point your only option is to create a new database and copy the data across. Due to customer outcry over SQL Server 2000 to 2005 migrations Microsoft added a scripting feature in SQL Server 2008 that can copy the structure and the data. For my 6GB database it generates 20GB of scripts that take nearly three hours to run.

For most people it isn't that big of an issue. I'm in the process of testing a migration from SQL Server 2005 to SQL Server 2008 and wanted to use the same data on both versions. Microsoft has made this scenario incredibly difficult.

Sidebar: Most Domino releases include a new file version, which is called the on disk structure or ODS. Domino does not automatically upgrade to the new ODS so you can decide when to upgrade. You can also downgrade ODS versions by using "compact -R". I would much prefer this to Microsoft's forcing the issue and not giving you any reasonable downgrade options.