MS SQL Server DB version without restoring?

Solution 1:

RESTORE HEADERONLY FROM DISK = 'pathTobackupFile'

The database version can be found in the column: DatabaseVersion

From Jonathan Kehayias blog post here the number in this column:

• SQL Server 7.0 databases have version number 515
• SQL Server 2000 databases have version number 539
• SQL Server 2005 databases have version number 611/612
• SQL Server 2008 databases have version number 655

[To those that want to edit this answer to include additional version numbers, that is not the intention of this answer. It was just for example and quoting the information that is from the link provided. The version number of a database can vary and may not be exact across every database based on what SP or CU is installed, or setting/feature that is being used. The version numbers are not documented by Microsoft and is just a simple reference to understand that the number increases as you move between SQL Server versions.]

If you try to run this command for a more recent database version than the one you're running it under (e.g. a 2012 DB from SSMS 2008), the database results display as INCOMPLETE

To check the build number of the instance check for columns:

• SoftwareVersionMajor
• SoftwareVersionMinor
• SoftwareVersionBuild

Documentation can be found here (TechNet link).