Is SQL Server Up-Level Compatible?

Q: Why can’t I restore a database from SQL Server 2008 to a SQL Server 2005 instance? It doesn’t work even when I use the 90-compatibility mode.

A: The simple answer is that SQL Server isn’t up-level compatible. A SQL Server version can’t understand databases from more recent SQL Server versions (i.e., SQL Server 2005 can understand SQL Server 2000 databases but not SQL Server 2008 databases). This is enforced through the database’s physical version number. A specific version of SQL Server creates databases with a specific version number (e.g., SQL Server 2008 databases have version number 655). When a database from an earlier version is attached or restored, a series of upgrade steps are performed on the database to bring its structures up to the correct version—and you can see this happening.

For instance, if I restore a SQL Server 2000 backup to a SQL Server 2008 instance, I see the following messages as part of the output from RESTORE:

Converting database ‘Pauls2000DB’ from version 539 to the current version 655.
Database ‘Pauls2000DB’ running the upgrade step from version 539 to version 551.
Database ‘Pauls2000DB’ running the upgrade step from version 551 to version 552.
Database ‘Pauls2000DB’ running the upgrade step from version 552 to version 611.

Database ‘Pauls2000DB’ running the upgrade step from version 653 to version 654.
Database ‘Pauls2000DB’ running the upgrade step from version 654 to version 655.

These upgrade steps are irrevocable, one-way operations—there’s absolutely no way (even undocumented) to downgrade a database to a lower version. For those of you who think you’ve seen this happen, you haven’t. Something prevented your database from being upgraded.

Each version of SQL Server has a maximum database physical version number that it can understand, and SQL Server will refuse to attach or restore a database with a higher version number. Unfortunately, the error messages that SQL Server provides aren’t that intuitive. (For more information about these error messages, see my blog post "Msg 602, Level 21, State 50, Line 1.")

Here’s an example of something that an earlier version of SQL Server can’t process: SQL Server 2005 log records contain extra fields such as the bitmap of which locks were held at the time the change described by the log record was made. SQL Server 2000 didn’t include these fields, so the SQL Server 2000 logging and recovery subsystem wouldn’t be able to process this information.

The database compatibility level has nothing to do with the database’s physical version number—it just controls some query processor behavior. With this in mind, be careful not to accidentally upgrade the “only” copy of a database if you think you’re going to have to go back to the earlier SQL Server version again, because your only option after the database has been upgraded is going to be extracting the data out into a new database (manually or using a third-party tool). The bottom line is that SQL Server isn’t up-level compatible.