In some cases it it interesting to know whether a database was directly created on the current version of the running SQL Server instance or if the database was upgraded during an upgrade of the instance or by attaching a database from an older version to a newer version of SQL Server.

The information is stored in the datafile headers. To reveal it you will have to use the DBCC command and redirect the information to the trace output (by default this will be send to the errorlog) by using the following command:

DBCC TRACEON(3604)

After that you free to use one of the following commands:

  • DBCC DBINFO (information of the current database you are executing from)
  • DBCC PAGE(‘YourDatabaseHere’1,9,3) (Whereas “YourDatabaseHere’1” is you database to check for)

to will get you the following information:

clip_image002

For the sample database I am using below (upgraded from version 2005 to 2008) this would bring back:

clip_image002[5]

You will have to take a look at dbi_version (current version of the database (files), this may changes over upgrades) and dbi_createVersion (Version from which the database was created with, this will never change)


-Jens