If you work for a product based organization, you would know that databases created in versions as old as SQL 7 are still around. The only thing is that over time, they have evolved via upgrade programs which have upgraded them to the latest and greatest levels – running on latest editions such as Microsoft SQL Server 2008 R2.

Whenever you work with a production database for troubleshooting or for an upgrade, have you ever wondered about the SQL Server version in which a particular database created? Answer to this question would answer questions such as:

1. How old is this database?
2. Has this database ever been reconstructed? (Reconstruction meaning data from an existing database pumped to a new database)

Getting answers to these questions is not that difficult, provided you can run a DBCC command against a database. All that needs to be done is:

DBCC DBINFO('AdventureWorks2008R2') WITH TABLERESULTS

That’s it! Well, let’s dissect the DBCC command that we used:

DBINFO: This, to the best of my knowledge, is an undocumented DBCC procedure. This means that it does not come with any sort of warranty from Microsoft (and also none from this site and me). It is therefore advised not to use this in production environments.

Next up, as the name suggests, DBINFO provides “insider” information about the database – something that is not available regularly via viewing the database options or via any DMV. The details of each and every result option is not in scope of this discussion. You may want to follow posts on SQLSkills [Paul Randall (blog)] for insights into DBCC from the guru himself.

TABLERESULTS: Most of us would have used DBCC CHECKDB to verify the integrity of our database. We get a whole lot of information as the output. However, not all DBCC commands return an output. The various output formats for DBCC that I know of are:

1. Output to client (i.e. – display “on screen” as is the case with DBCC CHECKDB)
2. Log to SQL Server Log
3. Provide output via the use of TRACE flag – 3604 (When researching this post, I could not find documentation on MSDN for this flag. In such cases, please follow the assumption I take – it’s undocumented, and therefore not covered by warranty)
4. In addition, we have a 4th output format – output as a table. The WITH TABLERESULTS clause specifies the results in a tabular format that can be loaded into a table.

By default DBCC DBINFO does not return any results. Therefore, we either need to use option #3 (TRACE Flags) or #4 to display the output of the DBINFO command. In the query I have mentioned above, we used the TABLERESULTS option.

The result

When I ran the above mentioned query against the AdventureWorks2008R2 sample database for Denali CTP03, the following was the output:

If we look at the fields – dbi_createVersion and dbi_crdate, we can see that they indicate the SQL Server version in which the database was created and creation date/time respectively.

For the AdventureWorks2008R2 for SQL 11 (“Denali”) CTP03, the values are:

• dbi_createVersion = 679
• dbi_crdate = 2010-08-02 17:26:22.770

So, now we know that this database was upgraded from another version of Microsoft SQL Server (dbi_createVersion is different when compared to dbi_version). Also, we know that the database was created first in August, 2010.

I trust that the above information will be helpful to you sometime in the future.

Until we meet next time,

Be courteous. Drive responsibly.