Query SQL DBs for their Version of origin

jjsather
jjsather used Ask the Experts™
on
After restoring several SQL DBs to my instance of SQL Server, is there any way to query the DBs to determine what version they came from before I restored them?

The query "select @@Version" appears to query my instance rather than the individual DB, but I'd like to know which DBs came from Express, the past version, etc.

Is that even possible?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
you can check the compatibility level  using the query below

SELECT * FROM sys.databases
Nakul VachhrajaniTechnical Architect, Capgemini India

Commented:
Yes. However, it is undocumented (to the best of my knowledge). You can use the following to get the basic information about any database:

DBCC DBINFO

Open in new window


The output should have a field - createdVersion (forgive me, I do not have SSMS in front of me right now). That will give you the version in which the database was created.
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>>  is there any way to query the DBs to determine what version they came from before I restored them?

Yes, you can find out where your database is originally created and not from where it was there earlier..
To be more clear, let's say a database was created in SQL Server 2005, later it was upgraded to SQL Server 2008 and if you have restored the database in SQL Server 2012, your dbi_Createversion will show as 611(SQL Server 2005) and dbi_version will show as 706(SQL Server 2012 current one). It will not show the SQL Server 2008 from where you have restored.

>> but I'd like to know which DBs came from Express, the past version, etc.
No, you can't get this.

Replace ur_db_name with your database name and run the below script.
 DBCC TRACEON (3604)
GO

DBCC PAGE ('ur_db_name',1,9,3)
GO

DBCC TRACEOFF (3604)
GO

Open in new window


Check the values dbi_version and dbi_CreateVersion columns from the Output result..
More info about SQL Server internal database versions here..
http://sqlserverbuilds.blogspot.com/2014/01/sql-server-internal-database-versions.html
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
@Raja,

Are you sure ? Never used dbcc page to get version info, but have used dbcc dbinfo

Both are undocumented, both are pretty safe to use...
DBCC TRACEON (3604)
GO

DBCC DBINFO (ur_db_name)
GO

DBCC TRACEOFF (3604)
GO

Open in new window

dbcc traceon(3604)  is only for the session, so, shouldnt have to turn it off, but guess it is safer to do so if you proceed onto other tasks in that session.

And yes, DBCC DBINFO() will produce dbi_Version and dbi_CreateVersion. For which the rest of your post describes very well :)
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
@jjsather,

Is there something else we can do for you ?

SQL doesnt keep the original edition of SQL Server (such as Express, Developer, Standard, Enterprise), but you can get Version information using DBINFO as shown above.  As you already know, @@version will give you the current edition, version and more, but not much else can be done as far as your needs go (well, as far as I know).
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> Are you sure ? Never used dbcc page to get version info, but have used dbcc dbinfo

yes, Mark. I've used couple of times but not frequently..

>> And yes, DBCC DBINFO() will produce dbi_Version and dbi_CreateVersion. For which the rest of your post describes very well :)

Thanks and happy to help and share..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial