Query SQL DBs for their Version of origin

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?
jjsatherAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
you can check the compatibility level  using the query below

SELECT * FROM sys.databases
0
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
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.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>>  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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Mark WillsTopic AdvisorCommented:
@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 :)
0
Mark WillsTopic AdvisorCommented:
@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).
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.