THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Database Version vs Database Compatibility Level

I've seen this one confuse people in the past, and the topic came up today on the forums so I thought I'd punch out a quick blog post about it.  In general, I'd say that most people know about database compatibility levels in SQL Server.  However if you don't know, the compatibility level provides partial backwards compatibility with previous versions of SQL Server to allow legacy code to continue to function on newer versions of SQL Server.  The currently available compatibility levels are:

  • 60 = SQL 6.0
  • 65 = SQL 6.5
  • 70 = SQL 7.0
  • 80 = SQL 2000
  • 90 = SQL 2005
  • 100 = SQL 2008

The current compatibility level of a database can be found using the DATABASEPROPERTY() or DATABASEPROPERTYEX() functions or in the sys.databases DMV.  More detailed information can be found in the books online topic ALTER DATABASE Compatibility Level (Transact-SQL) which also includes specific differences between compatibility level 80 and 90 and 100.  What the database compatibility level doesn't control is what version of SQL Server the database can be restored/attached to.  Setting a database in SQL Server 2008 to compatibility level 80 and then detaching it from the server won't allow it to be attached to a SQL Server 2000 instance.  If you try it, you will get an error similar to the following:

ExampleDB cannot be opened because it is version 655. This server supports version 539 and earlier.

The reason for this is that database internal version is for SQL Server 2008.  To find the internal version number of a database, you can use DBCC DBINFO to read the database header page:

DBCC TRACEON (3604); 
GO
DBCC DBINFO
GO
DBCC TRACEOFF (3604);

You can also read the page header by using DBCC PAGE:

DBCC TRACEON (3604); 
GO
DBCC PAGE (ExampleDB, 1, 9, 3);
GO
DBCC TRACEOFF (3604); 

The pertinent section from the output is:

DBINFO @0x00000000147EEA70 

dbi_dbid = 14                        dbi_status = 24                      dbi_nextid = 613577224
dbi_dbname
= ExampleDB               dbi_maxDbTimestamp = 176300          dbi_version = 655
dbi_createVersion
= 539              dbi_ESVersion = 0                    
dbi_nextseqnum
= 1900-01-01 00:00:00.000                                  dbi_crdate = 2009-05-18 14:09:05.813
dbi_filegeneration
= 0               
dbi_checkptLSN

The dbi_version shows the current version of the database.  You can also tell if the database was created in a previous version of SQL Server with the dbi_createVersion, which this database was created in SQL 2000 then migrated to SQL Server 2008.  When a database is opened for the first time on a newer version of  SQL Server, the database is automatically upgraded by the database engine.  If you were to make a backup of a SQL Server 2000 database named ExampleDB and restore it onto a SQL Server 2008 server, the following would be output during the restore process:

Processed 1412 pages for database 'ExampleDB', file 'ExampleDB' on file 1.
Processed 2 pages for database 'ExampleDB', file 'ExampleDB_log' on file 1.
Converting database 'ExampleDB' from version 539 to the current version 655.
Database 'ExampleDB' running the upgrade step from version 539 to version 551.
Database 'ExampleDB' running the upgrade step from version 551 to version 552.
Database 'ExampleDB' running the upgrade step from version 552 to version 611.
Database 'ExampleDB' running the upgrade step from version 611 to version 621.
Database 'ExampleDB' running the upgrade step from version 621 to version 622.
Database 'ExampleDB' running the upgrade step from version 622 to version 625.
Database 'ExampleDB' running the upgrade step from version 625 to version 626.
Database 'ExampleDB' running the upgrade step from version 626 to version 627.
Database 'ExampleDB' running the upgrade step from version 627 to version 628.
Database 'ExampleDB' running the upgrade step from version 628 to version 629.
Database 'ExampleDB' running the upgrade step from version 629 to version 630.
Database 'ExampleDB' running the upgrade step from version 630 to version 631.
Database 'ExampleDB' running the upgrade step from version 631 to version 632.
Database 'ExampleDB' running the upgrade step from version 632 to version 633.
Database 'ExampleDB' running the upgrade step from version 633 to version 634.
Database 'ExampleDB' running the upgrade step from version 634 to version 635.
Database 'ExampleDB' running the upgrade step from version 635 to version 636.
Database 'ExampleDB' running the upgrade step from version 636 to version 637.
Database 'ExampleDB' running the upgrade step from version 637 to version 638.
Database 'ExampleDB' running the upgrade step from version 638 to version 639.
Database 'ExampleDB' running the upgrade step from version 639 to version 640.
Database 'ExampleDB' running the upgrade step from version 640 to version 641.
Database 'ExampleDB' running the upgrade step from version 641 to version 642.
Database 'ExampleDB' running the upgrade step from version 642 to version 643.
Database 'ExampleDB' running the upgrade step from version 643 to version 644.
Database 'ExampleDB' running the upgrade step from version 644 to version 645.
Database 'ExampleDB' running the upgrade step from version 645 to version 646.
Database 'ExampleDB' running the upgrade step from version 646 to version 647.
Database 'ExampleDB' running the upgrade step from version 647 to version 648.
Database 'ExampleDB' running the upgrade step from version 648 to version 649.
Database 'ExampleDB' running the upgrade step from version 649 to version 650.
Database 'ExampleDB' running the upgrade step from version 650 to version 651.
Database 'ExampleDB' running the upgrade step from version 651 to version 652.
Database 'ExampleDB' running the upgrade step from version 652 to version 653.
Database 'ExampleDB' running the upgrade step from version 653 to version 654.
Database 'ExampleDB' running the upgrade step from version 654 to version 655.
RESTORE DATABASE successfully processed 1412 pages in 14.468 seconds (7.852 MB/sec).

The internal database versions for SQL aren't documented in the Books Online.  However, thanks to Paul Randal (@PaulRandal) and his Q&A article in TechNet we have the following list:

  • SQL Server 7.0 databases have version number 515
  • SQL Server 2000 databases have version number 539
  • SQL Server 2005 databases have version number 611/612
  • SQL Server 2008 databases have version number 655

If you want to see how to read the version information from a database that is not attached to a SQL Server Instance you can look at the following MSDN Forums post:

http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/3de5b574-0751-44a2-b69f-fa0c20378359

Many thanks to Paul Randal for answering questions about DBCC PAGE via twitter while on vacation, and for the Q&A article that provided the database version numbers back to SQL Server 7.0.

Published Tuesday, July 28, 2009 3:54 PM by Jonathan Kehayias

Comments

 

Rick Lively said:

here is some code that implements this... http://sankarreddy.spaces.live.com/blog/cns!1F1B61765691B5CD!463.entry

December 10, 2009 6:48 AM
 

Rob Kraft said:

If you want to see the DatabaseVersion without loading the database file run

RESTORE HEADERONLY FROM DISK=N'FILENAME' WITH NOUNLOAD

March 23, 2010 8:20 AM
 

anon said:

Rob, that command seems to work fine with backup files but not the MDF.

May 10, 2010 8:16 PM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement