Database internal file versions– How to determine if the database was upgraded or created from scratch

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:

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

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

Comments

• Anonymous
  September 05, 2011
  Hello, Really interesting post about a topic rarely studied. A little problem : if i have well understood the documentation of SQ Server 2008 and 2008 R2, this format of version is depreciated.So i would want 2 questions :

1. with what this version format will be replaced ? ( or is a feature that could be given up ? )
2. as i am interested by SMO ( always since 2005 ), do exist equivalent in SMO of dbi_createVersion and dbi_version ? Have a nice day PS : i am sad : nothing on this blog since August 2010 , i am regreating your posts short but always easy to understand ( especially for me with my poor english )